Text to Columns in Excel (Split Column, Text, Date, …)

Excel’s Text to Columns Wizard is a very important feature with many uses. In this article, I will teach you some useful uses of Text to Columns in Excel. Like you can split the columns of your database in your own way, you can extract important information from email address. Even I have also shown how you can use the advanced option of Text to Columns Wizard to convert trailing minus signs to negative.

Let’s start.

1) Let’s design a database with Text to Columns in Excel

When you design your database to store information in it, you have to remember one thing very carefully. Your every column should have a minimum amount of data in it.

Databases are powerful only if you make them powerful. How can you do that? How can you avoid the problems mentioned upstairs?

Let’s elaborate an example and work around with diagnosing the problems in the first place.

This is a sample database.

text to columns in excel

So our really important database is ready. Now I must be able to manipulate it to make it even more useful. I want to arrange the names alphabetically. Problem here? The first name and last name are together in the same columns.

I want to know how many people from Illinois are there on my list. Problem here? The whole address has been fed in one column.

I want to know how many 20-30 years olds I have.  You got it right. Cannot be done. Same column.

So you got the pattern, right? There is too much information in each column.

Had they been in different columns, it would have been much easier.

To solve these types of problems: you can use three tools:

  1. You can use Text to Columns feature in Excel
  2. You can use Flash Fill feature. Flash Fill feature is a new tool to Excel 2013
  3. And you can also use Excel function to divide a column data into ultimate small pieces.

To know how Flash Fill feature works in Excel, read this article: How to use Flash Fill in Excel 2013

Similarly, the 3rd option is also an entire topic covered here.

We will be focusing on Text to Columns feature.

Splitting the Names Using Text to Column Feature

Let’s first work with the Contact column. To use the Text to Column feature, you have to select the whole column. You can select the whole column by using the mouse (the old-school way). Or you can be efficient and use the keyboard shortcut which is to select the first cell and now press CTRL + SHIFT + Down Arrow.

Keyboard Shortcut to select a whole column

Select a whole column with keyboard shortcut

The whole column gets selected pretty easily.

Now open the Data ribbon and in the Data Tools group of commands, click on the Text to Columns feature.

Text to Column feature in Excel

A dialog box will appear.

Convert Text to Columns Wizard, Excel

This is the Convert Text to Columns Wizard and it is showing Step 1 of 3. By default, the Delimited radio button is selected.

For our case, Fixed Width option is not applicable. You can select that when you are sure of the width of the new columns and know the fact that the data you want will be truncated properly just going by dimensions. So Delimited is okay with our problem. Click on Next button.

Split Names in Excel

We enter into Step 2 of 3. Here we see some Delimiters are available. We know that space is the delimiter in our case. You will also be able to see a data preview in this window. Scroll down to see where the separated data is okay.

We click on Next button and we enter into the last step of the process.

Convert Text to Columns Wizard, Excel

Click on the image to get a full view

You can choose Column data format to General, by default it is selected.

If you select a date, you can also choose your format for date, by default DMY is selected in my case, DMY means Day, Month and Year, but there are other options in the drop down.

You can select your own. In the destination field, you will select the destination cell where your data will be set. I select the F1 cell.

Now I click on finish button here. This is the final outcome:

Splitting columns in Excel with Text to Columns feature

You see the Contact Column is separated into two columns. I name the first column as First Name and the second column as the Last Name. So, you see separating data from a single column containing name using Text to Columns feature is not that tough. But in some cases, you might face some problems.

Let’s Split the Addresses Now

Say if you try to separate the City State and ZIP column to its smaller Components: City, State, and Zip, then you might face a problem.

See this column has two types of delimiters: Comma and Space. In this case, you have to separate it in two steps. At first, I separate this column using the Comma delimiter.

Split Address in Excel

You see this column is divided into two parts. We name the first column as City.

Split data in Excel

Now we shall divide the column called State Zip using the Space delimiter.

Splitting data with Space delimiter, Excel Text to Columns feature

So, you get the three columns. Not that tough.

Using Text to Column For Converting Text to Date

Let’s try to work on the Date of Birth column.

The first part of this column is Month. Then the day part and then the year part. Here we are going to use the date format we read about a while ago.

Separate data in Excel

From this column data format, I select the Date button and then select the MDY option from the drop-down. I change the destination cell. And finally, I click on the Finish button.

Convert Text to Columns Wizard

Separated data

Click on the image to get a full view

You see how nicely the column has been converted to a date column.

There you go!! You see now how easy it is to use the Text to Columns feature to redesign a bad database.

2) Extract Domain and Username from Email IDs using Text to Column

The text to column feature provides an easy option for splitting email addresses into username and domain. To perform this 1st select the email ids that you want to be split then just like the previous steps begin to perform the Text to Column feature.

In step 1 of Converting Text to Column Wizard select Delimited and click Next.

In the 2nd step of the Converting Text to Column Wizard, put a tick mark on the Other option and write @ just beside it. Press on to the Next option.

In step to of Converting Text to Column Wizard put a tick mark on the Other option and write @ just beside it. Press on to the Next option.

In step 3 click on the destination and click Finish.

You will see the Usernames and Domains are divided into two columns.

3) Advanced Text to Columns for Negative Numbers

Suppose you have numbers in a column with trailing minus sign. To convert them into the actual negative numbers, select the numbers and begin to perform the Text to Column Feature. 1st select the cells containing the trailing minus numbers and then press onto the Text to column feature.

In step 1 of Converting Text to Column Wizard select Delimited and click Next.

In step 2 in the Delimiters option, select nothing and press Next.

In step three, press on the Advanced option and in the Advanced Text Import Settings put a tick on the Trailing minus for the negative numbers and then press finish.

Advanced Text to Columns

You will see the below result after this.

Download working file

Database_Design.xlsx

Related Readings


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply