Text to Column - - - Just Smart Enough

by Sijia Wei

One thing Carl spotted from my first Friday’s presentation was that the ‘Text to Column’ tool can be used to split the text entry in one column into multiples based on multiple delimiters.

For example, we want to split:


The ‘Text to Column’ tool actually allows us to finish this step in one go. In this case, we write ‘\s-‘ under the ‘Delimiters’, for ‘space’ and ‘-’. We’ll see from the output that our target column splits into 3.

OK, what about we give it a more difficult task? If our input column is:

Is it smart enough to understand if we write ‘\s-;’ under the ‘Delimiters’?

Surprisingly, it does the job regardless of the number and the sequence of the delimiters I’ve typed in!

Noticed that we have an extra empty column, and this was because we had nothing in-between the two ‘-’s. So remember to give your output extra columns if you have delimiters next to each other.