Return to Answer

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.

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.

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 of this approach.

Leverage Google Apps Scripting to extend the UI

Go to:

Tools → Script editor...

Paste the following:

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.

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:

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

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

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.

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.

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