If you reformat data brought into Excel from an external source regularly then you’ve most likely come across Excel’s Text to Columns tool.
You can also reformat text using formulas like MID, SEARCH, LEFT and RIGHT to name a few but this can be tedious and hurt your head 🙂
If you perform the same
head text splitting task regularly then a template can save you loads of time, and that’s just what we’ve got for you here:
Catalin, our in house Excel Guru has put this template together.
All you have to do is paste your data in column A starting in row 6 and enter your delimiter in cell B3 and you’re done 🙂 Use it yourself or read Catalin’s explanation of ‘how it’s made’ and learn something new.
Enter your email address below to download the sample workbook.
There are 4 templates in this file:
- Text to columns - single delimiters
- Text to Columns - consecutive delimiters
- Text to Columns – portable version
- Text to Columns – VBA UDF version
Functions used in this template:
COLUMN – returns the column number of a reference e.g. =COLUMN() in cell B2 would return a 2 as, column B is the second column.
RIGHT – returns the specified number of characters from the end of a text string.
LEFT - returns the specified number of characters from the beginning of a text string.
I'd like to say a big thanks to Catalin for creating this template. If you want to say thanks to Catalin for sharing this file and his knowledge you can leave him a comment below.