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.
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:
- You can use Text to Columns feature in Excel
- You can use Flash Fill feature. Flash Fill feature is a new tool to Excel 2013
- 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.
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.
A dialog box will appear.
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.
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.
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:
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.
You see this column is divided into two parts. We name the first column as City.
Now we shall divide the column called State Zip using the Space delimiter.
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.
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.
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.