Google sheets split and unique

Question

I have a column and each cell has multiple labels, like so:

a,b,c
b, c, d
g, h, a

So I'm looking for a formula, not code, where the formula finds the unique elements for the column,

output:

a
b
c
d
g
h

I've tried different combinations of unique(), split(), arrayformula(), transpose() but can't get it work.

=unique(ArrayFormula(TRANSPOSE(split(A1:A3,","))))

not sure how to get it into one column

Answer 1

You can use this formula: =unique(transpose(arrayformula(trim(split(join(",",A1:A),",")))))

Assuming that the delimiter is always a comma - the easiest way to stack them would be to use join with the same delimiter as well.

That way when you split it, they will all be split with the same delimiter.

In order to clean up the extra spaces, you want to wrap that in an arrayformula trim around your split formula, and finally transpose the list and wrap with unique.

Answer 2

So this might not be the most elegant answer, but I took your original formula and tweaked it a bit. The missing pieces were: joining your array into a comma-separated string in a single cell, then using SPLIT and TRANSPOSE to get it into a column. Then you just run UNIQUE on it:

=unique(TRANSPOSE(SPLIT(TEXTJOIN(",",TRUE,ArrayFormula(TRANSPOSE(TRIM(split(A1:A3,","))))),",",TRUE)))

I also added a TRIM in there to take care of the white space around some of your letters.