This post explains how to correctly split comma-separated values in a multi-column table in Google Sheets.

Google Sheets has two built-in options to separate values in a column based on the delimiters.

1. Using the Split function.
2. Using the Data menu ‘Split text to columns’ command.

The first one is a formula approach, and of course, following it has the benefit of using a formula.

For example, we can use the Transpose function with Split to change the data orientation after the split.

To split comma-separated values in a multi-column table, we will use the Split function in Google Sheets.

Assume we want to assign the same score to multiple people. If so, we may comma separate the names in a cell and their score in another cell.

For example, the scores of candidates A, B, and C is 95. The score of candidate D is 94. We can enter their data in two ways.

Type # 1

Type # 2

Type # 2 has one main benefit.

We can aggregate the type # 2 data using Query easily because it’s formatted as a database table.

Further, in such a dataset, we can use the Sumif math function or Lookup function conveniently.

From my point of view, one of the benefits of type # 1 data is it saves some rows. So when we print the data, we can use a fewer number of papers.

Assume our data entry operator has entered the data as per type # 1. Can we format it as type # 2?

Yes! Let’s start with the basics to split comma-separated values in Google Sheets.

Split Multi-Row Comma Delimited Values (Basic)

If we have only a single column, for example, the first column in type # 1, we can use the Textjoin, Split, and Trim functions as an array formula as below.

Formula A

=ArrayFormula(transpose(trim(split(TEXTJOIN(",",true,A1:A3),","))))

Here is the formula explanation.

Textjoin – Join all values in A1:A3. We should check the existing delimiter in column A and use the same to join the values.

Split – To split values. The result will be in a single row.

Trim – To remove white spaces. For example, see the value in cell A1. There is a white space after every comma. When the formula splits, it will remain with the split values.

Transpose – To change the orientation of the values from row to column.

Split Comma-Separated Values in a Multi-Column Table

It is the topic of this tutorial. Let’s learn it and its alternative formula (yes, I have two solutions) in detail.

Two Column Table

Here I am starting with a two-column table.

I will try to explain it in detail. That will help you to change the formula to accommodate more than two columns in the table.

My sample data to split is in A2:B6. But I will use a large range A2:B1000 in the formula.

Find the below formula in cell D2.

Formula B

=Query(ArrayFormula(split(trim(flatten(split(A2:A1000,",")&"🐠"&B2:B1000)),"🐠")),"Select * where Col2 is not null")

The above result is an example of splitting comma-separated values in a multi-column table in Google Sheets.

Some of you can read and understand the formula. For those who want to learn it step by step, read the formula explanations below.

Steps

In the steps, I’ll use the actual range A2:B6, not A2:B1000. That will make the steps easy to follow.

1. The below formula first splits the values in A2:A6 and then adds (combine) the values in B2:B6 with all the split values.

To make the values in both the columns separate, I”ve used the fish delimiter.

=ArrayFormula(split(A2:A6,",")&"🐠"&B2:B6)

I have used the 🐠 character as the delimiter to make the formula attractive. You can use any delimiter of your choice, such as a pipe symbol on your keyboard.

2. There is a relatively new ‘official’ function in Google Sheets called Flatten.

By wrapping the above formula with Flatten, we can make multiple column values into a single column as below.

=ArrayFormula(FLATTEN(split(A2:A6,",")&"🐠"&B2:B6))

3. By using the Trim function, we can remove the unwanted space character in values.

=ArrayFormula(trim(FLATTEN(split(A2:A6,",")&"🐠"&B2:B6)))

4. Let’s split this output again. This time the delimiter is the “fish” character.

=ArrayFormula(split(trim(FLATTEN(split(A2:A6,",")&"🐠"&B2:B6)),"🐠"))

5. Just filter the rows in the output wherever column 2 has no value. For that, we can use the function Query.

That is my final formula. Find the same (formula B) just below the title Split Values in a Multi-Column Table in Google Sheets.

Three or More Column Table

I have a three-column table. That means, this time, there is one additional column.

So, we need to make two changes to the formula. The changes are in the second and fifth steps.

Here are the formula and its result.

Formula C

=Query(ArrayFormula(split(trim(flatten(split(A2:A1000,",")&"🐠"&B2:B1000&"🐠"&C2:C1000)),"🐠")),"Select * where Col3 is not null")

What about the changes?

In the second step, I have added the &"🐠"&C2:C1000 part to include the third column.

If you have one more column, repeat this part and change the column reference from C2:C1000 to D2:D1000.

In Query, i.e., in the fifth step, instead of Select * where Col2 is not null, used Select * where Col3 is not null.

If you have a fourth column in your table, replace this part to Select * where Col4 is not null.

This way, we can split and organize data in Google Sheets.

Alternative Formula to Split Comma-Separated Values in a Multi-Column Table

In the beginning, I have mentioned that Flatten is a new function. That doesn’t mean there was no solution in Google Sheets to split comma-separated values in a multi-column table in Google Sheets.

We can replace Formula B above with the below formula that doesn’t involve Flatten.

=ArrayFormula(split(transpose(trim(split(textjoin("🌼",1,if(len(A2:A1000),REGEXREPLACE(A2:A1000&",",",","🌼"&B2:B1000&","),)),","))),"🌼"))

What about Formula C?

Here is its alternative.

=ArrayFormula(split(transpose(trim(split(textjoin("🌼",1,if(len(A2:A1000),REGEXREPLACE(A2:A1000&",",",","🌼"&B2:B1000&"🌼"&C2:C&","),)),","))),"🌼"))

If you are learning Google Sheets, then you may want an explanation of the above formulas too.

You may find that here – Split to Column and Categorize – Google Sheets Formula.

Thanks for the stay. Enjoy!