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.

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

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:see=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:

also compatible with excel 2003

roberto mensa

Hi I’m thinking about something like this:

in B6

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