Split one column into multiple columns in Excel [Text to Columns]

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.

Related: Create Database in Excel in 8 Easy Steps

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.


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.

Use Text to Columns feature in Excel

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

Click on the image to get a full view

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, 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.

Convert Text to Columns Wizard, Excel

Click on the image to get a full view

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

Click on the image to get a full view

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

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 data into multiple parts with Text to Columns feature in Excel

Click on the image to get a full view

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

Split data in Excel

Click on the image to get a full view

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

Splitting data with Space delimiter, Excel Text to Columns feature

Click on the image to get a full view

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

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

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

Click on the image to get a full view

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

Click on the image to get a full view

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.

Download working file

Database_Design.xlsx

Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.