0

I have a sheet that has a column with labels in a single cell. A cell can contain 1 or more labels, each separated by a comma.

+-----------+
| Labels    |
+-----------+
| Foo,Bar   |
| Bar,Baz   |
| Quux,Quuz |

I want to create a formula that generates a a column with all the unique labels separately in a row. For the example above this would be the following.

+-----------+
| Labels    |
+-----------+
| Foo       |
| Bar       | 
| Baz       |
| Quux      |
| Quuz      |

I have figured out that I can use the SPLIT function to obtain the labels separately. If I add to that the use of ARRAYFORMULA I can get a matrix of the values. But then I'm stuck with that matrix, and how to turn all the columns into a single column. To solve that I know you can use the {RANGE, RANGE} syntax. To apply the {} to the result of an ARRAYFORMULA I use a QUERY to pick out all the columns.

This gives me the following:

=UNIQUE(
  {
    QUERY(TRANSPOSE(ARRAYFORMULA(SPLIT(A1:A4, ","))), "Select Col1");
    QUERY(TRANSPOSE(ARRAYFORMULA(SPLIT(A1:A4, ","))), "Select Col2");
    QUERY(TRANSPOSE(ARRAYFORMULA(SPLIT(A1:A4, ","))), "Select Col3")
  }
)

The problem with this approach is that it works for Labels that contain up to three comma-separated values, and there has to be at least one cell with three comma-separated values.

1

I think this will do just what you need.
Give it a try

=QUERY(INDEX(IFERROR(FLATTEN(SPLIT(A2:A9,",")))),"WHERE Col1<>'' ",0)
1
  • Have you tried the given answer? If it works for you it would be nice to accept. If not let me know so I can fix it. – marikamitsos Apr 4 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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