Return to Revisions

It's a commonly used feature of Excel that Google Spreadsheets still lacks. Fortunately, there are workarounds that will do the same or better.

##Use a function

In cell B2 paste the following:

=ARRAYFORMULA(IFERROR(SPLIT(A1:A;"|")))

Then paste your data starting with cell A2. As soon as you enter the data it will automatically split the combined values to a value-per-column using the '|' as a delimiter.

Here's credit to the original author of this approach.

Leverage Google Apps Scripting to extend the UI

Go to:

Tools → Script editor...

Paste the following:

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.

To use it:

• Select the cell containing the values
• Select Advanced → Text to columns

It's magic. It'll automatically split the values out into columns. If you were wondering, the non-custom "Text to column" function uses commas as separators.

If you haven't noticed yet, this option comes with an added bonus, there are options in the menu that will reverse the process.

Best of all, because this works as a function you can roll it into any script. For example, if you want you could get rid of the menu and make a column automatically expand values by triggering the function during an onEdit event.

Note: If you take a good long hard look at the Text to columns code it may seem a little complicated. That's because it has been optimized to cache all of the values, pre-calculate all of the results and load them all at once (vs per-row). Unfortunately, the setValues function doesn't accept jagged arrays so I had to roll a solution to pad all of the rows to the same length before loading the results.

Update:

The code has been updated to include the ability to convert multiple cells at once.

Here's a link to a working demonstration. You'll need to wait for a few seconds after the spreadsheet loads but a new 'Advanced' toolbar menu will popup that has the text-to-columns and columns-to-text options.