Excel — Text to Column and TESTSPLIT formula
Whenever you have data in a cell that you want to split this is a handy tip.
Option #1 Use can select the text you want to split and click on the Text to Columns command on the Data TAB.
Using this command will replace your existing data. Here’s an example.
Select your data and click on Text to column. Selected Delimited. This will let you choose the character to split. In this example we will split using a SPACE.
If your selected data does not have enough columns to the right to split your data you will get an pop up asking if you want to proceed.
Clicking OK will produce the below results. Note the amount column and the original cells have been overwritten with the split data.
To perform Text to Columns without losing the amounts, you would need to inset enough blank columns to accommodate you split data.
Option #2 — Use the Text split formula. You can enter this Formula in any cell and simply reference the data you need to split. It doesn’t need to be next to your original data.
In this example the text in formula is cell A28 and the col_delimiter is the SPACE or “ ”
Here are the results.
If you happen to enter the TEXTSPLIT formula without leaving enough cells for the results — Excel will return a #SPLILL! Error.
All you need to do is insert the needed columns, and Excel will automatically ‘spill’ your data.
Test this out. No need to retype first and last names or endless cutting and pasting — Excel has a formula for that.
