Extract unique characters from cell range

[ollyhughes1982]

Hi all.

I am trying to extract a list of unique characters from a range of cells. I did this previously, when i had a list 999 rows long, but now my list has grown to 2,551 rows and when I amend my reference to 2552, instead of 1000 it gives ma an error. Please can someone help. I can't understand why I can't just change the formula from:

=LET(u,UNIQUE(FILTER(A2:A1000,A2:A1000<>"")),UNICHAR(UNIQUE(UNICODE(MID(CONCAT(u),SEQUENCE(LEN(CONCAT(u))),1)))))
to
=LET(u,UNIQUE(FILTER(A2:A2552,A2:A2552<>"")),UNICHAR(UNIQUE(UNICODE(MID(CONCAT(u),SEQUENCE(LEN(CONCAT(u))),1))))).

Shouldn't this simple range change just work?

Link to file (it's very small and opens quickly) is here: All Characters List.xlsx

Thanks in advance!

Olly.

 

[StephenCrump]

Presumably with the bigger range, the CONCAT string will be exceeding the 32,767 character cell limit.

Another way you could do this:

=LET(data,A2:A2552,r,ROWS(data),c,MAX(LEN(data)),txt,MID(data,SEQUENCE(,c),1),s,SEQUENCE(r*c,,0),x,INDEX(txt,1+s/c,MOD(s,c)+1),UNICHAR(UNIQUE(UNICODE(FILTER(x,x<>"")))))

 

[ollyhughes1982]

Presumably with the bigger range, the CONCAT string will be exceeding the 32,767 character cell limit.

Another way you could do this:

=LET(data,A2:A2552,r,ROWS(data),c,MAX(LEN(data)),txt,MID(data,SEQUENCE(,c),1),s,SEQUENCE(r*c,,0),x,INDEX(txt,1+s/c,MOD(s,c)+1),UNICHAR(UNIQUE(UNICODE(FILTER(x,x<>"")))))

Ok, great. Thank you, I will use this instead.