# In Google Sheets, how to achieve the “unwinding” of rows in a table that contains columns with multi-valued data

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

### 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)

• Did this answer work for you? Jul 31, 2023 at 20:59

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