How do you do a text-to-columns conversion in Google Spreadsheets?
For example, I have the following data string in one cell:
I want to break it apart by the comma separator into columns.
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:
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.
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:
And the next; text-to-row:
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.
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
And an equivalent of columnToText would be to use the
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:
Can be useful for retrieving the number of results for a Google query for example
With the new version of Google Spreadsheets, Google App Scripts is now deprecated.
You can use the Power Tools of the Google Add-on Gallery and use the Split function. It works well.