Extract part of a text string using Text to Columns

There are many not-so-easily-understood terms in Excel. “Text to Columns” should be one of them.  Probably due to the name of it, Text to Columns is not a common tool for many Excel users.  But indeed, if you know what it does, you will love it.

What is Text to Columns?

It is one of my best buddies in Excel.

It is a great tool to extract specific text from a text string without the use of a single formula.  It is a great news for those who are not familiar with formula yet, isn’t it?

When you hover your mouse on the icon of Text to Columns under Data Tab on Ribbon, you will see the description about it…

Excel Tips - Text to Columns

Is it much clear now?

Tip: You may see a better description and sometimes even tip of using a tool when you hover your mouse onto its icon on Ribbon.   Try the same to Format Painter.

Most of the time, we will use a “Delimiter” for Text to Columns.  And the “Fixed width” is somehow forgotten.  In some occassions, “Fixed width” is very handy and useful when we deal with text string with constant pattern.

In this blogpost, I will focus on using “Fixed width”.

Let’s put it into action

You may download a Sample File – Text to Columns to follow through.

Excel Tips - Text to Columns 1

The task (to extract the date portion, and turn it into real date) is simple from the point of view of requestor, most of the time your boss.  🙂

Nevertheless, if you are not familiar with text manipulated functions like LEFT, MID, RIGHT…,  the task may be a challenge to you.

The good news is: With Text to Columns, this task can be done with just a few clicks… Wanna count how many clicks required?Excel Tips - Text to Columns (Fixed width)

Here’s the step-by-step instruction:

  • Select the range of cells
  • Go to Data –> Text to Columns
  • In the following wizard:  Select “Fixed width“, then Next.Excel Tips - Text to Columns 2
  • At the preview window, click where you want a column break.
  • The next step (3 of 3) is the most magical step of Text to Columns.  It allows user to set certain data type, which could be extremely useful.  In our case, we need to tell Excel that our data are Date in DMY format.  (Note: If your data is in MDY format, select MDY instead)

Excel Tips - Text to Columns 4.1

  • As we want the date portion only, we click on the other column and then select “Do not import column (skip)

Excel Tips - Text to Columns 4.2Tip: I have changed the “Destination:” to $C$2 because I want to extract the date from the original data, that I want to retain.

Note: By default, the Destination is set to the upper left cell of the range selected.  By this default, original data will be overwritten by the “extract” texts.

Here we go:

Excel Tips - Text to Columns 1

Super easy, isn’t it?

Because we are dealing with text string with constant pattern – Date with 8-digit in DDMMYYYY format, “Fixed width” does the job nicely.  Even if we want more, e.g. have DD, MM, YYYY extracted into three different columns.  What we need is just a little twist in Step 2 of 3 in the Text to Columns Wizard:

Excel Tips - Text to Columns 5

And don’t forget to change the Destination:

Excel Tips - Text to Columns 6

As simple as this! 🙂

Excel Tips - Text to Columns 7

With this technique, we can extract any specific portion of fixed length from a text string easily.  E.g. 4 characters on the 5th position, 1st two characters, etc.

How about if we also need Latitude and Longitude?? Apparently, they are not of “Fixed width” because of the negative sign…

No worry!  Did you know that we have the “Delimited” option in Step 1?  Let’s talk about it in next post.  Stay tuned.

You may also like:

Other posts related to Text to Columns


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.