How do you do a text-to-columns conversion in Google Spreadsheets?
For example, I have the following data string in one cell:
5,233,6,2,6,7,2,2,6,6
I want to break it apart by the comma separator into columns.
Edit: I changed the accepted answer to one that doesn't use Google Apps Scripting because Google seems hell-bent on nerfing it's ability.
Text to Columns, is a very handy feature and one of the reasons that a lot of Google Spreadsheet users go back to using Excel. Until, Google decides to officially support the feature, this solution can be used as a polyfill to add in the functionality.
Here's the code:
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];
menuEntries.push({ name:"Text to columns", functionName:"textToColumns" });
menuEntries.push({ name:"Text to columns (custom separator)", functionName:"textToColumnsCustom" });
menuEntries.push(null);
menuEntries.push({ name:"Columns to Text", functionName:"columnsToText" });
menuEntries.push({ name:"Columns to Text (custom separator)", functionName:"columnsToTextCustom" });
ss.addMenu("Advanced", menuEntries);
}
function textToColumnsCustom() {
var separator = Browser.inputBox("Text to column","Enter the the separator",Browser.Buttons.OK);
textToColumns(separator);
}
function columnsToTextCustom() {
var separator = Browser.inputBox("Column to text","Enter the the separator",Browser.Buttons.OK);
columnsToText(separator);
}
// Expands a single cell of CSV formatted text to multiple columns
function textToColumns(separator) {
var sep = typeof(separator) !== 'undefined' ? separator : ',';
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveRange();
// check that only one column was selected
var col = r.getColumn();
if(col !== r.getLastColumn()) {
Browser.msgBox("Error", "Invalid selection, too many columns.", Browser.Buttons.OK);
return;
}
var firstRow = r.getRow();
// short cut the one row selection
if(firstRow === r.getLastRow()) {
var values = r.getValues().toString().split(sep);
ss.getRange(firstRow,col+1,1,values.length).setValues(new Array(values));
return;
} else {
var rows = r.getValues();
var values = [];
var cols = 0;
for(var i = 0, len = rows.length; i < len; i++) {
var rowValues = rows[i].toString().split(sep);
var rowValuesLen = rowValues.length;
if(cols < rowValuesLen) { cols = rowValuesLen; }
values.push(rowValues);
}
// set all values at once (padding required because setValues doesn't accept jagged 2d arrays)
padRow(values, cols);
ss.getRange(firstRow,col+1,values.length,cols).setValues(values);
}
}
// Pads a row with empty values to the specified length
function padRow(array, length) {
for(var i = 0; i < array.length; i++) {
var arrLen = array[i].length;
if(arrLen < length) {
var padLen = length - arrLen;
var padding = new Array(padLen);
array[i].push.apply(array[i], padding);
for(var j = 0, len = array[i].length; j < len; j++) {
if(typeof(array[i][j]) === 'undefined') {
array[i][j] = "";
}
}
}
}
return array;
}
function columnsToText(separator) {
var sep = typeof(separator) !== 'undefined' ? separator : ',';
var ss = SpreadsheetApp.getActiveSheet();
var r = ss.getActiveRange();
var col = r.getColumn();
var firstRow = r.getRow();
var rows = r.getValues();
var values = [];
for(var i = 0, len = rows.length; i < len; i++) {
var value = rows[i].join(sep);
values[i] = [value];
}
col -= 1;
ss.getRange(firstRow,col,values.length,1).setValues(values);
}
Save and close the script editor. Then, refresh the spreadsheet. It'll take a second to load but you should see a menu called 'Advanced' popup after 'Help' in the toolbar.
Usage:
That's it. You can also do a split using a custom delimiter (via 'Text to Columns (Custom)') and reverse the process (via 'Columns to Text').
The follow formula will do just that; text-to-column:
A1=5,233,6,2,6,7,2,2,6,6
A2=SPLIT(A1;",")
And the next; text-to-row:
A1=5,233,6,2,6,7,2,2,6,6
A2=TRANSPOSE(SPLIT(A1;","))
UPDATE 03-02-2013
If you split the result of A1 and paste the values, it will give the same result as all the lines of code used in the OP's answer. I gave it a shot at it as well with Google Apps Script and this is what I created: text to column
function mySplit() {
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sh.getActiveCell().getValues()[0];
var sCell = cell[0].split(",");
var row = sh.getActiveCell().getRowIndex();
var col = sh.getActiveCell().getColumnIndex();
sh.getRange(row,col+1,1,sCell.length).setValues([sCell]);
}
I simple use the build-in split function to split the result and add it it to the sheet, nothing more and nothing less.
I used the split function, and it worked perfectly, since it uses the continue formula as well and it does exactly the translation from text on column A to columns BCDE with proper spacing.
My example:
Cell A1= text1, text2, date1, number1
Cell B1= split(A1,",")
Outcome in B1 is text1. Outcome in C1 is text2 Outcome in D1 is date1 Outcome in E1 is number1.
It takes care of the format, since the date was spelled as 1-jun, and it translated to 01/06.
The formulas that split used in cells BCDE where CONTINUE(B1; 1; 2), CONTINUE(B1; 1; 3), CONTINUE(B1; 1; 4). All of this part was created automatically.
I really like Evan's answer using Apps Script, and made a small improvement: adding support for regular expression delimiter matching. To the menuEntries in onOpen I added:
menuEntries.push({
name: "Text to columns (regular expression separator)",
functionName:"textToColumnsRegExp"
});
And added the referenced function:
function textToColumnsRegExp() {
var separator = Browser.inputBox(
"Text to column",
"Enter the regular expression for the separator ",
Browser.Buttons.OK);
if (separator) {
textToColumns(new RegExp(separator));
}
}
No other changes were required because Evan is using Javascript's String.prototype.split, which accepts either a string or a RegExp object as the delimiter. So kudos the Evan!
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);
Furthermore, after using the SPLIT function which will provide the array containing the separated values, you can isolate a specific row or column from this array with the INDEX function:
=index(split(importXML("https://www.google.com/search?q=stackexchange","//div[@id='resultStats']/text()")," "),1,2,1)
Can be useful for retrieving the number of results for a Google query for example
There appears to be a menu item that can help with this, at: Data → Split text to columns...
Single click on a cell and paste your data.
It will appear over multiple rows but only one column.
Leaving the newly populated cells highlighted, go to the menu Data → Split text to columns...
If the app managed to auto-detect your separators, then congratulations: you are done!
Otherwise, a little widget will appear asking what separator you want to split on.
Note: this widget may appear near the bottom of the window making it a little hard to find!
Use the widget to choose which separator your data is delimited by: Comma, Semicolon, Period, Space or Custom.
If you wanted to split on Tabs:
And an equivalent of columnToText would be to use the =JOIN(delim, array) formula. For example, =JOIN(",", A1:A10) would result it concatenated string of values from cells A1 thru A10.
