**Download this tutorial (zipped PDF)**
A common problem that many people have is where they have a spreadsheet where data in a single column needs to be split into multiple columns. For example a list of employee names where both First Name and Surname are in the same cell or a list of customers where the entire address is in a single cell, as opposed to one cell for street, one of city, one for postcode, etc.
In the following example, Pauline, an HR Manager, receives an Excel file every Friday, containing a list of contractors who are due to leave the company the following week. The Excel file is generated by running a report from a specially designed HR system and selecting the "save as Excel" option when the report is run.
Pauline has to copy and paste the contractor names into another spreadsheet but before she can do that she has to separate the First Name and Surname into separate cells. Unfortunately the way the HR system extracts the names, it stores each contractor’s First Name and Surname in a single cell. In addition, the HR system stores and extracts the names in upper case whereas Pauline requires the names to be displayed in proper case (i.e. Mike Thomas not MIKE THOMAS)
This tutorial uses Excel 2010 but the same functionality can be achieved in all versions of Excel.
Convert From Upper Case to Proper Case – 1
Each cell in the list in column A contains a First Name and Surname in upper case. The first task is to convert the contents of each cell to proper case. This is done by using the Proper function.
In B1, type =Proper(A1) and press the Enter key. This instructs Excel to display the contents of A1 in proper case.
Convert From Upper Case to Proper Case – 2
Copy the function in B1 to B2:B6.
Each cell in column B now contains a function that converts the contents of the cell in column A (on each row) to proper case.
Convert From Upper Case to Proper Case – 3
The next step is to replace the data in column A with the data in column B. This cannot be achieved by simply using cut and paste because the cells in column B contain functions, not values. Each function converts the cell one to the left to proper case (i.e. the function in B1 is =Proper(A1) and A1 is one cell to the left of B1), therefore if you cut or copy B1 and paste into A1, you will get an error because the function (that is pasted into A1) will be trying to convert the cell one to the left of A1 (which is non-existent) to proper case.
The correct way to replace the data in column A with the data in column B is to select the data in column B and copy it….
Convert From Upper Case to Proper Case – 4
…then click on A1
Click on the arrow at the bottom of the Paste button (make sure you do not click the Paste button itself)
Click the left-most button in the Paste Values section
This pastes the results of the functions in column B into column A
*In earlier versions of Excel, you need to select Paste Special and then select Values
Convert From Upper Case to Proper Case – 5
The final step in the first part of the process is to delete the data in column B so you are left with the required names in column A
Splitting the Names into Separate Cells – 1
The next part of the process is to split the contents of each cell into 2 cells (i.e. Mark in A1 and Booth in B1)
Ensure that you have selected all the cells in column A that contain the names (A1:A6)
Click the Data tab on the Ribbon and click the Text to Columns button
Splitting the Names into Separate Cells – 2
Step 1 of the Convert Text to Columns Wizard is used to specify whether the data is Delimited or Fixed Width. In this example, because the names are different lengths (and therefore are not fixed width), select Delimited (1) and click the Next button (2)
Splitting the Names into Separate Cells – 3
Step 2 of the Convert Text to Columns Wizard is used to specify which character is used as the delimiter. In our example, each First Name and Surname is separated by a space, therefore tick Space and untick any of the other Delimiter checkboxes. Click the Next button
Splitting the Names into Separate Cells – 4
Step 3 of the Convert Text to Columns Wizard is used to specify the destination of the data. As previously noted, the requirement is to have the list of contractor names starting in A1, therefore click in the Destination field and click on A1. Don’t worry that Excel puts $ signs around the cell reference.
Also note that it is not necessary to specify a range of cells. The list will start at A1 and extend across one column and down as many cells as were selected when the "Text to Columns" command was issued.
Click the Finish button to complete the process
Splitting the Names into Separate Cells – 5
The data is now laid out exactly how Pauline, our imaginary HR Manager, requires it for her report – and the whole process took about 2 minutes!