Excel Series: Text to Columns – Part 1

Introduction

Before we dig into Text to Columns, let’s read a nursery rhyme…

Humpty Dumpty sat on a wall,

Humpty Dumpty had a great fall;

All the king’s horses and all the king’s men

Couldn’t put Humpty together again.

Mother Goose
humpty dumpty and excel

What does Humpty Dumpty have to do with Excel? Everything! (Except in Excel we can also put it back together again, but that’s a topic for another day).

So, you must have guessed, the Excel equivalent of Humpty Dumpty, is called Text to Columns.

What is Text to Columns?

In summary, Text to Columns is a tool in Excel that allows the user to split content from one column across multiple columns. It is especially useful when data is exported from another system (e.g. SAP or VIP), and all the information is pasted in a single column. Consequently, this is often the case with .csv files.

BeforeAfter
User 1 – [email protected]User 1[email protected]
User 2 – [email protected]User 2[email protected]
User 3 – [email protected]User 3[email protected]

How to use it?

Before jumping in to using the Text to Columns tool, prepare your data:

  1. Ensure there is a blank column next to your data
  2. Failure to do this will overwrite your existing information
  3. Find the “common factor” to split your column text. This could be:
    1. A delimiter (e.g. a comma, space, period)
    2. Based on fixed width – this adds a break at a specific point in your content (not always possible if information is not consistent)

Now, you may proceed to:

  1. Select your range (one column at a time)
  2. Navigate to Data -> Data Tools -> Text to Columns
  3. Choose how you wish to split your information
text to columns screenshot
  • On the following screens select the options you require for your information.
  • Voilà! You have successfully split your information into two (or more) columns.
text to columns in excel

More than meets the eye

In our Excel Intermediate course we explore some of the special features of the Text to Columns tool, with examples that delve deeper than this blog post. Make sure you book your spot at our next upcoming course!

As a preview of what you can find in the course, part 2 of this blog post will dig into some of these features too. In the meantime, enjoy making scrambled eggs ?

How have you used Text to Columns? When have you wished you knew about it? Leave your comments below.