One of the great features of Excel, is its ability to read data from a CSV file.
This gives it the ability to import almost any type of information into the spreadsheet.
But once it has been imported, it isn’t always in the most suitable format.
A point in case is the following sheet which contains names and addresses.
While the information is well formatted and readable, it doesn’t lend itself easily if we wanted, for example, to sort the names alphabetically by last name, or the addresses by state.
In such cases, it would really help if the names were split into first and last names, and the addresses into street address, city, county, state, and zip code.
As it turns out, with Excel, this is quite easy to do.
While the process itself is the same, the difference between splitting the names and splitting the addresses, is in the delimiter.
In our example sheet, the first and last names in the NAME column are separated by a space, while the street, city, county, state and zip code in each address are all separated by line breaks.
We’ll see how to handle both the space and the line break.
Ideally, we would want to split the existing NAME column into two columns that are next to each other.
So, the first step is to insert an empty column between the exisiting NAME and ADDRESS columns.
This empty column will hold the last name once the NAME column is split.
To accomplish this, right mouse click on the letter B in the column grid at the top of the sheet to bring up the context menu, and choose the Insert option.
Now we can proceed with splitting the names into first name and last name.
Select the A column, and then in the Data tab, select the Text to Columns option to bring up the Convert Text to Columns Wizard.
The first step in the wizard asks if the names will be split by looking for a delimiter, or if the first and last names occupy a fixed width.
Delimited will work fine in most cases, but there are times when Fixed width is also required.
We will use Delimited since our names are of varying lengths and separated by a space.
Click the Next button to proceed to the next step in the wizard.
This is where we define how the names, will be split.
Since our names are separated by a space, we select the Space checkbox.
At the bottom of the wizard dialog box is the Data preview section.
Here, you can see how changing the various settings affects the outcome of the split.
Selecting the Treat consecutive delimiters as one checkbox means that if there are two or more consecutive spaces between the first and last names, they are treated as one single space.
If this wasn’t checked and there was more than one space between any of the names, the result would be something like the first name shown in the Data preview of the preceding snapshot, which is not what we want.
With all the options set correctly, click the Next button to go to the third and final screen of the wizard.
There are a few options to check in this screen before completing the wizard.
Firstly, looking at the Data preview section, notice that while the names have been perfectly split into two columns, the formatting selected by excel is General.
Leaving it at General will still work, but since our names are text and as an exercise, we will change the format by selecting the column in the Data preview section (the first name column is initially selected by default), and then going to the Column data format section at the top of the wizard screen and selecting the Text radio button.
That takes care of the first column, so we now select the second column in the Data preview section, which contains the last names, and again select the Text radio button from the Column data format section.
That’s all there is to it.
With the column formatting out of the way, the only other choice left is the Destination field, which in this case contains the cell coordinates $A$1.
This is where our original, NAMES column resides, and is where by default, the split columns will be written to.
Remember how before opening the wizard, we inserted a blank column to the right of the original NAMES column?
It was for this reason, so that the wizard would have somewhere to write out the results of the split without overwriting any existing content.
If we had not created the blank column and had left the ADDRESS column where it was, after completing the split, the last names in the second column shown in the Data preview would overwrite the ADDRESS column (which we don’t want of course).
You could also set the Destination field to another column, perhaps something like $D$1, one that you know is blank.
Setting the destination to something different than the original, source column, allows you to keep the original NAMES column, as well as create two new columns with the split names, in this case starting in column D.
This is the result if we were to set the Destination to cell address $D$1. Both the original NAME field, and the split columns are now available.
In some instances, this may be desirable.
Otherwise, leaving the Destination with the default setting produces the above result.
Notice how the NAME column now only contains the first names.
The contents have been overwritten by the first column from the split.
While column B now contains the last names.
We’ve successfully performed the column split.
We are now going to split the ADDRESS column into 5 columns and separate it into the street address, city, county, state, and zip code.
The process is identical to how we split the NAMES column.
If there are other columns to the right of the ADDRESS column, you will need to insert four blank columns so that the split does not overwrite existing content.
Select the ADDRESS column, and then from the DATA tab click on Text to Columns to open the Convert Text to Columns Wizard.
Notice how the Preview field has all the address information for each row, bunched up.
We will take care of this in the next step.
As before, we are going to use a delimiter to split the addresses.
Click the Next button.
The difference here from when we split the names, is in the delimiter.
Make sure that in the Delimiters section, only Other is checked.
Left mouse click inside the entry field to the right of the Other checkbox, and press Ctrl-J.
Ctrl-J is the line break character, but it is a non-printing character so all you’ll see is a blinking full stop.
More importantly though, the Data preview should now show the addresses all nicely split up, after which you can go ahead and click the Next button.
The only thing left to do is change the format of each column from General to Text, although as before, General will also work just fine.
Click the Finish button to affect the split.
The result should look something like the above.
All that remains is to give each column its correct heading and we are now ready to sort our sheet by last name, state, or any other field.
As we’ve seen, splitting a single column into multiple columns is a breeze with Excel.
