**Update: This script can now be installed via the 'Script Gallery'. That means you can easily install it into your spreadsheet without dealing with any code. Just install the script named 'Text to Columns' from the gallery, reload the spreadsheet, and wait a few seconds for the 'Advanced' toolbar item to appear.**

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][1] 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.

  [1]: https://groups.google.com/a/googleproductforums.com/forum/#!category-topic/docs/formatting/9lFTpwUiN1I
  [2]: https://docs.google.com/spreadsheet/ccc?key=0AmqaAZD3swQDdHVqZTh0bjQydkprS3RFSXNWS09sRGc