(UPDATE) DEFINING exact length of string for ISTEXT within AGGREGATE function

Hi forum,

I have this formula (it was given to me by a member of this forum few days ago):

=IFERROR(AGGREGATE(15,6,$M$25:$W$29/ISTEXT($M$25:$W$29)/(--$M$25:$W$29>MAX(A$33:A33)),1),"")

From the sample attached, I need the 5 digits jobs to go only in the table FULL LOADS and the 4 digits jobs to go only in the table JOBS.

How can integrate a check in the formula so that it is calculated only for a string of a certain length?

Any help would be greatly appreciated!

Thank you,
Alberto

Please try At A32

=IFERROR(AGGREGATE(15,6,$M$25:$W$27/ISTEXT($M$25:$W$27)/(LEN($M$25:$W$27)=5)/(--$M$25:$W$27>MAX(A$31:A31)),1),"")

Change Number of digit in Red if needed.

Hi Bo_Roy,
Hi All,

I have another question regarding this formula.
Is there a way to make it work, in a specific table, for string GREATER than a certain length?

I tried this: =IFERROR(AGGREGATE(15,6,$M$25:$W$27/ISTEXT($M$25:$W$27)/(LEN($M$25:$W$27)>5)/(--$M$25:$W$27>MAX(A$31:A31)),1),"") but it did not work.

I attach sample for reference.

Any help would be appreciated!

Thanks,
Alberto

Formula only work with number
Please unmerge cell and try at A32
=SORT(UNIQUE(FILTER(INDEX(M25:X27,SEQUENCE(18,,,1/6),MOD(SEQUENCE(18,,0,2),12)+1),LEN(INDEX(M25:X27,SEQUENCE(18,,,1/6),MOD(SEQUENCE(18,,0,2),12)+1))>=5)))

P.S. Thanks, Malta is unique and beautiful country. I have a plan to visits someday.

Hi Bo_Ry,

I am having an issue with this formula, actually.
First, it spills on the below cells (it does not seem to really affect the results since it still shows them as intended); is it ok?
Second, if I change the range of the array from M25:X27 to M6:X27, it gives an EMPTY ARRAY error (CALC) although the cell interval is not empty.

EDIT: this issue seems to happen with Office 36 for Windows and not for MAC. I will check better later and post an update.

Thanks for any advice!
Alberto

Spill is from New Dynamic array. Array formula will spill all data in array. No more copy formula down.
The Calc is error from Filter function when array is empty.
Add ,"" to return null string when array is empty. and correct Sequnece

=SORT(UNIQUE(FILTER(INDEX(M6:X27,SEQUENCE(ROWS(M6:X27)*6,,,1/6),MOD(SEQUENCE(ROWS(M6:X27)*6,,0,2),12)+1),LEN(INDEX(M6:X27,SEQUENCE(ROWS(M6:X27)*6,,,1/6),MOD(SEQUENCE(ROWS(M6:X27)*6,,0,2),12)+1))>=5,"")))