Excel Series: Text to Columns – Part 2

Welcome to the second post that considers what Humpty Dumpty and Excel has in common! In part 1 we saw that this special tool is called Text to Columns.

In our first post we alluded to some special features that users do not often know about, or do not understand. Today, we will consider a few of these.

Numbers to Text

If you have ever had to use Excel to capture cellphone or ID numbers, you may have wished there was an easy way to convert numbers to text. (And, surprise surprise, there is!)

For example, some ways in which Excel may mistreat information include:

You may know that to force Excel to read a number as text, you can add a single quotation mark before you type the number.

However, if you already have a long list of ID numbers (or any other numbers) that need to be read as text, it is not an easy endeavor to add single quotation marks before all these numbers, nor will changing the format of the cells to text be the solution.

So, cue Text to Columns! On the third screen of your text to columns dialog, you will see a variety of options:

• General
• Text
• Date
• Do not import column

The second option, “Text”, will therefore, give us our desired results:

This will solve the problem of our ID number, but not yet our cellphone number.

The cellphone number will require one further step where a CONCATENATE function adds a 0 before the new text.

This option can also be used with lookup functions where information has two different types of formats – either change both to number (using the General option), or both to text (using the Text option).

Numbers to Dates

The third option gives the user the option to convert numbers into dates, or more commonly, text that already looks like a date but does not pick up a date format.

The reasons for this could be:

• Dates in Excel are in a different order to your computer settings (for example, Excel dates are input as dd/mm/yy instead of yy/mm/dd)
• Inconsistent separators are used between the date, month or year (for example, spaces or full stops)
• The date has been typed using a text format

For example, in the screen capture below, you see how to use Text to Columns to convert these items to dates. The date order option in the third screen must match the date order of the text (e.g. DMY or YMD).

Skip columns

The last option allows you to pick and choose the columns you would like to include in your Excel file after splitting the information across multiple columns.

This is useful if:

• there is superfluous information that has resulted from text to columns
• there are additional columns that might overwrite data in your sheet
• there are empty columns after using text to columns

Importantly, note that it is also possible to use any of the above options on the resulting columns in your Text to Columns wizard. In other words, if you have five columns, each of these columns could have a different option.

Before:

Text to Columns options:

After:

Conclusion

In conclusion, after reading this post, you should have a pretty good idea of what all the options in Text to Columns entail! If you would like more examples of how this could be used, remember to attend our next Excel Intermediate workshop.

We would love to hear how this fantastic tool has helped to save you time! Drop us a comment below ?