In each cell of my column G, i have a list of elements like this :

['element1','element2','element3',...]

In some cells i have the same list, but not ordered in the same way:

For example, in G2 i have ['element1','element2','element3'] and in G250 i have ['element3','element1','element2'].

I did a script to order each list alphabetically (to compare if they are the same), but i want it to be automatic because the values come from an import range, and data can be added every day.

So i tried a formula : =trim(substitute(concatenate(transpose(sort(transpose(split(G2,"'"))))),",","")) that works for what i'm doing with it. The result looks like this : []element1element2element3.

But i cant use arrayformula =arrayformula(trim(substitute(concatenate(transpose(sort(transpose(split(G2:G,"'"))))),",","")))

I have an error

The textual result of CONCATENATE exceeds the allowed limit, which is 50000 characters.

After some research on internet, it seems that the error come from concatenate (i tried with join, and it doesnt work either).

There isn't 50k characters in my lists so i suppose that it's trying to concatenate all the cells of my array formula.

CodePudding user response：

SORT each row after SPLITting using BYROW and CONCATENATE:

=BYROW(G2:G7,LAMBDA(row,CONCATENATE(SORT(TRANSPOSE(SPLIT(row, "[',']"))))))