2

In Google Sheets, how can I achieve the following “unwinding” of rows in a table that contains columns with multi-valued data:

Column A Column B, multivalued
First A,B
Second C,D

It transforms the above to:

Column A Column B, multivalued
First A
First B
Second C
Second D

How can I do this easily and quickly without having to manually split the data one row by one row?

2 Answers 2

0

My Sample Data

A B                                                                                                 
1 First A,B
2 Second C,D
3 Third E,F
4

Basic Formula

The formula uses the SPLIT function combined with ARRAYFORMULA to return an array made up of the first column followed by the columns returned by splitting the second column.

=ARRAYFORMULA({A1:A3, 
   SPLIT(B1:B3, ",")})

Basic Formula

Error Handling

Please note that SPLIT (syntax: SPLIT(text, delimiter)) will return a VALUE! error if the text argument is a blank cell. This can be avoided by wrapping it in an IFERROR function.

=ARRAYFORMULA({A1:A3, 
   IFERROR(IFERROR(SPLIT(B1:B3, ",")))})

Error Handling

QUERY (full columns)

Here is an example that uses complete columns and then QUERY to return only rows where column A is populated.

=QUERY(ARRAYFORMULA(
   {A:A, IFERROR(SPLIT(B:B, ","))}),
   "SELECT * where Col1 is not NULL") 

QUERY (full columns)

1
  • Did this answer work for you?
    – Blindspots
    Jul 31, 2023 at 20:59
-2

You can use the data->”split text to columns” function for this. Choose “,” as the separator in this case.

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.