Function to split text in cell and create column

Question

I have a Google Spreadsheet with rows like this:

| sth1, sth2 |
| sth4       |
| sth1, sth3 |
| sth4       |

And I want to split each cell in this column and create new column like this:

| sth1 |
| sth2 |
| sth4 |
| sth1 |
| sth3 |
| sth4 |

Can someone show me how to do this?

Answer 1

With Google Apps Script, it can be done with this little script:

Code

function splitColumn(range) {
  var output = [];

  for(var i in range) {
    var split = range[i][0].split(",");

    if(split.length == 1) {
      output.push([split[0]]);
    } else {
      for(var j in split) {
        output.push([split[j]]);
      }
    }
  }
  return output;  
}

Usage

Example

I've created an example file for you: row content to column

Remark

Add this script by selecting Tools from the menu, followed by Script editor. Paste the script and press the save button and you're on the go.

Answer 2

Assuming sth1, sth2 is in B1, does not require a script:

=ArrayFormula(transpose(split(textjoin(",",,B1:B4),", ")))