How to Split a Comma-Separated List of URLs From a Column Into Separate Rows in Google Sheets
If you have a spreadsheet that has a column with a comma-separated list of values (like URLs), you can easily combine them and create a separate row for each one in Google Sheets using a formula like this:
=TRANSPOSE(SPLIT(JOIN(",", Sheet1!B2:B), ","))
The formula above references column B of Sheet1, where each row's Column B is a comma-separated list of URLs for each product on a Shopify store.
Here's what the formula does:
JOIN(",", Sheet1!B2:B): This part of the formula combines all the comma-separated lists of URLs in Column B into a single text string, separating them with a comma (
SPLIT(..., ","): The
SPLITfunction then splits the combined text string into an array of individual URLs, using the comma as the separator.
TRANSPOSE(...): Finally, the
TRANSPOSEfunction transposes the array of URLs into separate rows.
Here's an example of what the data on Sheet1 looks like, which is basically a list of Shopify product URLs by collection for each product:
And here's the output of the formula on Sheet2 (referencing the data from Column B of Sheet1):
As you can see, this is a quick and easy way to combine data from one column and then split them up into separate rows for a more readable output.
- How to Import a Range From Another Sheet Within the Same Spreadsheet in Google Sheets
- Google Sheets Tip: Compare Two Columns and Extract Missing Values
- Google Sheets Tip: Generate JSON Data from Rows and Columns
- Google Sheets Tip: Generate a Comma-Separated List of Values From a Column
- How to Display Images from URLs in Your CSV File Using Google Sheets