Transform a range with comma-separated values into a column

This script is part of the Keywords in Sheets Free Add-On.

With this script you can input a range that includes cells with comma-separated values and the formula will return all values in a single column. Perfect for getting related keywords and top search results all in one column 🚀

Check out the GIF to see it in action 🎥

Get the Google Sheets Add-On

Learn more below 👇

How to add the Script to Google Sheets

1. Copy the script below:

/**
 * Transform a range with comma-separated values into a column
 * @param {A2:A100} range - your range (can be across multiple columns and rows). 
 * @customfunction
 */

function rowtocol(range) {

  // if no value return error
  if (!range) return 'You need to enter a range.'

  // if a single value transform into array
  if (!Array.isArray(range)) range = [range.split(',')];
  
  // loop through the array and split each value into an array
  var output = [];
  for (var i = 0; i < range.length; i++) {
    for (var j = 0; j < range[i].length; j++) {
      const row = range[i][j].split(',');
      for (k = 0; k < row.length; k++) {
        if (row[k] !== '') output.push(row[k].trim());
      }
    }
  }
  
return output; 

}

2. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Appscript from the Extensions menu.

Paste the script and save it.

3. Add the formula to any cell in your sheet

=rowtocol(A2:A100)

Replace A2:A100 with any range you want to transform.

This script is useful for analysing the comma-separated data from the search results functions inside of the Keywords in Sheets Add-on.

Thanks for stopping by 👋

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓

Questions? Get in touch with me on social or comment below 👇