Are you tired of dealing with comma-separated values in Google Sheets? Have no fear! In this article, I’ll show you two simple methods to tackle this common problem. So grab a cup of coffee, sit back, and let’s get started!
The Split Function: A Formula Approach
One way to separate values in a column based on delimiters is by using the Split function. This method offers the advantage of using a formula. For example, you can utilize the Transpose function with Split to change the data orientation after the split.
Let’s consider a scenario where you want to assign the same score to multiple people. You can enter the names in one cell and their score in another cell, separated by commas.
There are two types of data entry methods:
- Type #1
- Type #2
While Type #1 may save some space and paper when printing, Type #2 allows for easier aggregation and the use of functions like Sumif or Lookup.
But don’t worry! Even if your data entry operator has entered the data as Type #1, you can still format it as Type #2. Let’s dive into the basics of splitting comma-separated values in Google Sheets.
Split Multi-Row Comma-Delimited Values (Basic)
If you have a single column, such as the first column in Type #1, you can use the Textjoin, Split, and Trim functions as an array formula. Here’s an example of the formula:
=ArrayFormula(transpose(trim(split(TEXTJOIN(",",true,A1:A3),","))))This formula joins all the values in column A, splits them based on the comma delimiter, trims any whitespace, and finally transposes the values from row to column.
Split Comma-Separated Values in a Multi-Column Table
Now, let’s tackle the main topic of this tutorial: splitting comma-separated values in a multi-column table. I’ll provide you with two solutions to accomplish this.
Two-Column Table
Suppose you have a two-column table, like the one shown in the image below.
To split the values in this table, you can use the following formula in cell D2:
=Query(ArrayFormula(split(trim(flatten(split(A2:A1000,",")&"🐠"&B2:B1000)),"🐠")),"Select * where Col2 is not null")This formula splits the values in column A, combines them with the values in column B using a fish delimiter, flattens the table, and then splits the data again based on the fish delimiter. Finally, the formula filters out the rows where column 2 has no value.
Three or More Column Table
If you have a three or more column table, you’ll need to make a few adjustments to the formula. Let’s take a look at the modified formula for a three-column table:
=Query(ArrayFormula(split(trim(flatten(split(A2:A1000,",")&"🐠"&B2:B1000&"🐠"&C2:C1000)),"🐠")),"Select * where Col3 is not null")By following this pattern, you can accommodate additional columns by repeating the process and adjusting the column references accordingly.
An Alternative Formula: Say Goodbye to Flatten
In the beginning, I mentioned the Flatten function, which is relatively new in Google Sheets. But don’t worry if you prefer an alternative solution. You can use a formula that doesn’t involve Flatten. Let me walk you through it:
For the two-column table:
=ArrayFormula(split(transpose(trim(split(textjoin("🌼",1,if(len(A2:A1000),REGEXREPLACE(A2:A1000&",",",","🌼"&B2:B1000&","))),","))),"🌼"))And for the three or more column table:
=ArrayFormula(split(transpose(trim(split(textjoin("🌼",1,if(len(A2:A1000),REGEXREPLACE(A2:A1000&",",",","🌼"&B2:B1000&"🌼"&C2:C&","))),","))),"🌼"))These formulas follow a similar step-by-step approach as the previous ones, but with slight modifications to accommodate additional columns.
Conclusion
Congratulations! You’ve now mastered the art of splitting comma-separated values in a multi-column table in Google Sheets using two different methods. Whether you choose to use the Split function or the alternative formula, you have the tools to handle this task with ease.
If you’d like to explore more possibilities and enhance your Google Sheets skills, be sure to check out Crawlan.com for valuable resources and helpful tutorials.
Thank you for joining me on this journey. Happy Splitting!
