Return to Revisions

Leverage Google Apps Scripting to extend the UI

Update: This script is available in the Script Gallery. Just search for 'Text to Columns', install the script, and refresh the spreadsheet. Note: it may be slow to load the first time.

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:

• Select the cell(s) containing the values to split
• Select Advanced → Text to columns

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').