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

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,



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


not sure how to get it into one column

2 Answers 2


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.

enter image description here


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:


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.