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.
Thanks
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.
Excel Table Absolute Structured References
Thomby
Thank you for this! Im an excel newb and my situation requires a space delimiter along with a “/”. For example, Id like to split this : 173sx ss +1a/22507 into 173sx, ss, +1a, 22507. How can i do this with your template? TIA
Catalin Bombea
Hi Thomby,
You have more than 1 type of delimiter, there is no ready made formula for this, it needs to be built for this specific purpose.
Most likely, a vba user defined function will be the best choice. Can you post your question on our forum so we can work on a solution for you? (open a new topic after sign-up)
jim
you could add a helper column to Catalin’s table which uses SUBSTITUTE to convert all spaces to / and then that is used as the source data for the rest of the table
Rishi Kumar
Don’t make it complicated.
Just check below one.
=MID(SUBSTITUTE($D$2136,$E$2135,””),COLUMN()-5,1)
Catalin Bombea
Hi Rishi,
What is the formula for? Can you clarify what you have in those 2 cells $D$2136,$E$2135?
Kakrishna
It was wonderful. I would like to learn more
Mynda Treacy
Glad you liked it, Kakrisna 🙂 I hope you discover more on our site that’s of interest to you.
Mynda
jef
Great. Worked perfectly. Thanks for sharing.
Philip Treacy
thanks Jef 🙂
Imran
Dear Mynda, Great Job
Philip Treacy
On behalf of Catalin, thanks Imran.
Subash
Hi,
Thank you very much for taking the time to share this experience Catalin.
Thank you for posting the same on this great informative blog Mynda.
It is indeed very helpful in most of the scenarios. Although, I have a scene when I have dates sent to me in a format which is not excel compliant (in text format even if I change the same to number or date it doesn’t help) so I have to send text to columns and then use the formula for joining the three columns with “&” to get the format for excel to understand it as a date for calculating number of days.
I tried putting a date in the place of Sunday/Monday/Tuesday and it returned the text format of the date in column B (08/14/14 or 08-04-14 {I changed the delimiter to -} returned 41855). Is there a way around this please.
Thank you in advance.
Warm Regards,
Subash
Catalin Bombea
Hi Subash,
Thanks for your kind words 🙂
If you have 3 columns with year, month, day, you don’t have to join them with & (to concatenate them), just use the Date function:
=date(year,month,day). In these arguments, set the references to the appropriate columns, like:=DATE(A1, B1, C1), even DATEVALUE will work:
=DATEVALUE(A1&”/”&B1&”/”&C1)
If you have dates in first column, that’s another problem: dates are in fact numbers; doesn’t matter how you format the cells to see that date , that cell will always have a number, in decimal system , without any delimiter. In other words, what you see is not what you have in that cell. Dates cannot be “split” with text to column tools, only with simple formulas, like:
=Year(A1), or Month(A1), and Day(A1).
Hope it’s clear enough 🙂
Catalin
Steve Davis
Thank you very much for taking the time to share this little exercise Catalin.
Steve.
Catalin Bombea
You’re wellcome Steve, it’s more like an exercise on “How to think in Excel” to build formulas, at least, that’s what i consider it, the file containes detailed explanations on the “construction” process.
Cheers,
Catalin
roberto mensa
another alternative:
=IF(LEN($A6)-LEN(SUBSTITUTE($A6,$B$3,""))+1<COLUMNS($B12:B12),"",TRIM(MID(SUBSTITUTE($A6,$B$3,REPT(" ",999),COLUMNS($B12:B12)),FIND("§",SUBSTITUTE($B$3&$A6,$B$3,"§",COLUMNS($B12:B12))),999)))also compatible with excel 2003
roberto mensa
Hi I’m thinking about something like this:
in B6
=TRIM(MID(SUBSTITUTE($A6,$B$3,REPT(" ",999)),(COLUMNS($B7:B7)-1)*999+1,999))regards
r
Mynda Treacy
🙂 Thanks, Roberto. There is no end to your creativity!
Kris
I prefer this version with simplified column reference – also in B6:
=TRIM(MID(SUBSTITUTE($A6,$B$3,REPT(” “,999)),(COLUMN(A1)-1)*999+1,999))
What do you think? 😉
Cheers,
Kris
Mynda Treacy
Hi Kris,
I like yours much better than Roberto’s 😉
Mynda
Catalin Bombea
It’s a lot simpler and easier to understand, thanks for sharing 🙂 . Our text to column version is an exercise to help people understand how to “think in excel”, the file has very detailed explanations for each step of the process.I agree that your approach is the best ,i wrote a few years ago a formula using the delimiter substitution technique, to extract only a specified element from the text string. It’s the equivalent formula for VBA’s Split method. For those interested, the formula can be found on our OneDrive folder.
Cheers,
Catalin