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?
With Google Apps Script, it can be done with this little script:
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;
}
I've created an example file for you: row content to column
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.
Assuming sth1, sth2 is in B1, does not require a script:
=ArrayFormula(transpose(split(textjoin(",",,B1:B4),", ")))
I wrote this function to split a particular column.
function SplitColumnValues(sheet_name, column_index, delimiter) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheet_name);
var lastRow = sheet.getLastRow();
var range = sheet.getRange(1,column_index,lastRow-1, 1);
range.splitTextToColumns(delimiter);
}
The function takes sheet name, Column that you want to split and delimiter.
First, get the reference sheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheet_name);
next line will get the number of rows that are in the column that needs to be split
var lastRow = sheet.getLastRow();
next get the range(i.e. the column that needs to be split) and then split that range using 'splitTextToColumn'
var range = sheet.getRange(1,column_index,lastRow-1, 1);
range.splitTextToColumns(delimiter);
