In the previous post, we talked about the basic of Text to Columns and focused on “Fixed width”.  In this post, we will continue to talk about three magical things that Text to Columns could do with ease:

1. Extract part of text string with delimiter(s)
2. Convert numbers with trailing minus, e.g. “123-” into number
3. Convert numbers with “,” (comma) as decimal point into number

You may probably know about the first one,  but be honest did you also know the second and third one?  🙂  Let’s take a quick tour to all of them.

You may download the Sample File – Text to Columns (2) to follow through.

Extract part of text string with delimiter (s)

Situation: This is quite common, I guess.  Let’s continue with the example discussed in the previous post.  The task is to extract Latitude and Longitude from the text string in column A:

No matter whether we deploy “Fixed width” or “Delimited”, we will go through the 3 steps of the Text to Columns Wizard.  But first of all, we need to select the range of data before activating the Wizard:

1. Go to Data –> Text to Columns –> Select “Delimited”
2. Select “Comma” as Delimiterooops… Something went wrong as seen in the “Data preview” because we had another delimiter “|” there
3. Did you notice that the word “Delimiters” comes with “s”…  Check the “Other:” and specify “|” 
4. Since we do not need “Date”, we may “Skip” that column. 
5. And we want to extract the data into new column, we have changed the Destination to G2.
6. Here we go.

The advantage of “Delimited” over “Fixed width” is obvious.  It is applicable to most of the real world data that is

1. of different length;
2. separated by a common delimiter, e.g. comma, tab.

As illurstrated in the above example, Text to Columns can deal with more than one delimiters (up to 5).  Having said that, I hope you would never encounter a situation where you need to check more than 2 delimiters in Step 2 of the Wizard.  In my experience, the more delimiters you need, the less chance you will get your expected result.  🙂

Convert numbers with trailing minus

Situation:  You download some txt. file from an accounting system… you are surprised that all negative numbers are recorded with trailing minus, e.g. 123-.  All these numbers are treated as “text” and you cannot do simple calculation such as SUM as a result.  How frustrating it is…

No worry!  Text to Columns to rescue.  

In step 3 of the Wizard, there is an “Advanced…” button for data of “General” format.  Select the column of data with trailing minus, follow the steps below:

wow… it’s done!  Can’t believe it is as simple as that.

Convert numbers with a different decimal point

Situation: You work in a multi-national company.  And you know there are many countries in this world where they use a comma “,” (not “.” dot) as a decimal separator in the number.  You receive txt. file that store numbers in that way, i.e. 2,30 means 2.30 to you but a text string of “2,30” to Excel.  OMG… you can’t SUM these numbers. 😦

Again, Text to Columns to rescue.

In our previous example of using “Advanced…” in step 3 of the wizard, did you notice that you can specify Decimal / Thousand separator?

wow… again as simple as this.

Tip: We can do the conversion in a reversed direction.

Limitation of Text to Columns

Results are static.  When new data comes or original data changed, you may need to go through the same process again.  Power Query fixes this limitation.  In Power Query, we can “record” the steps, when data changes or updates, a click of “Refresh” does all the magic.