I’m working with a Google Sheets spreadsheet where one column contains multiple values separated by commas. I need to break these values apart so each value gets its own row, while keeping the other column data the same for each split.
For example, I have this data:
Column X
Column Y
5
Red, Blue
7
Green
I want to transform it to:
Column X
Column Y
5
Red
5
Blue
7
Green
Is there a built-in function or formula in Google Sheets that can do this automatically? I know pandas has a similar feature but I need to do this directly in the spreadsheet.
i like using Google Apps Script for this, it’s much more efficient than using formulas. you can write a quick function to loop over your data, split the values, and then create new rows easily. works great for larger datasets, no alignment issues!
Hit this same problem last month with survey data. SPLIT works but gets messy with alignment. I had better luck combining QUERY and REGEX. Try: =QUERY(SPLIT(FLATTEN(B2:B&","&A2:A), ","), "where Col1 is not null") - you’ll need to tweak the structure though. But honestly? The most reliable way I found was just using the built-in Data menu. Go to Data > Split text to columns, pick your comma-separated column, choose comma as delimiter, then copy and paste rows as needed. More manual work but you get complete control and skip the formula headaches.
To split comma-separated values into multiple rows in Google Sheets while maintaining the associated data in other columns, you can start by using the =SPLIT(B2, ",") function in a new column. This will break the values apart horizontally. Next, to convert these into a vertical list, apply =ARRAYFORMULA(FLATTEN(SPLIT(B:B, ","))). Be cautious, as this method might lead to misaligned data for large datasets. For more complex cases, consider utilizing Google Apps Script. If your dataset is small, another option is to copy the split values and use “Paste Special > Transpose” to rearrange them.