Extract part of text string with delimiter(s)… and more with Text to Columns

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:

Exce Tip - Text to Columns_Delimited 3.PNG

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”Exce Tip - Text to Columns_Delimited 1
  2. Select “Comma” as DelimiterExce Tip - Text to Columns_Delimited 1.1ooops… 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 “|”  Exce Tip - Text to Columns_Delimited 1.2
  4. Since we do not need “Date”, we may “Skip” that column.  Exce Tip - Text to Columns_Delimited 2
  5. And we want to extract the data into new column, we have changed the Destination to G2.Exce Tip - Text to Columns_Delimited 2.1
  6. Here we go.Exce Tip - Text to Columns_Delimited 3

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…Exce Tip - Text to Columns_Delimited 5

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:

Exce Tip - Text to Columns_Delimited 4 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. 😦Exce Tip - Text to Columns_Delimited 7

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?

Exce Tip - Text to Columns_Delimited 6

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.

 

Advertisements
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

2 Responses to Extract part of text string with delimiter(s)… and more with Text to Columns

  1. Frank Franco says:

    How cna you split one cell has wrap text into others columns in using text to Columns format??  

    Sent: Friday, October 13, 2017 at 8:03 PM

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s