I’m working with a Google Sheets spreadsheet where one column contains multiple values separated by commas. I need to break these comma-delimited entries into individual rows while keeping the corresponding values from other columns intact.
For example, my current data looks like this:
Column A
Column B
1
P, Q
2
Q
I want to transform it so each comma-separated item gets its own row:
Column A
Column B
1
P
1
Q
2
Q
Is there a built-in function or formula in Google Sheets that can accomplish this data transformation? I’m looking for something similar to pandas explode functionality but native to Google Sheets.
honestly just use SPLIT differently - put =SPLIT(B2,",") then manually copy column A down for each split value. not pretty but it’s quick and won’t break like complex formulas
QUERY + REGEXREPLACE works way better than splitting methods for this. I ran into the same issue restructuring inventory data and found this formula: =QUERY(ARRAYFORMULA({REGEXREPLACE(A2:A&"|"&B2:B,",","|"&A2:A&"|")}),"SELECT Col1 WHERE Col1 != ''") - just make sure your data’s set up right first. Honestly though, Power Query in Excel then importing to Sheets is your most reliable bet. If you’re stuck with pure Google Sheets, write a simple script that loops through and creates new rows. Way more consistent than formulas. The built-in functions get messy when your comma-separated values have different lengths per row and you need to keep everything linked up.
You can achieve this by using a combination of SPLIT, ARRAYFORMULA, and FLATTEN, although it requires some setup. First, create a helper column with =ARRAYFORMULA(SPLIT(B:B, ",")) to separate your comma-separated values. Next, employ =ARRAYFORMULA(FLATTEN(SPLIT(B2:B, ","))) to consolidate everything into a single column. The challenge lies in maintaining the associations with Column A. For a more seamless approach, I’ve found using Apps Script to write a custom function gives the best results. This method iterates through each row, separates the values, and generates the necessary new rows. While this may not be as straightforward as pandas’ explode function, it effectively manages the transformation without requiring manual duplication of values from other columns.