You may have come across the situation where you have a list of names in Excel, with each name in a single cell and you want to separate out the first name and surname into separate cells. You can do this with the Text to Columns feature but this has limitations. Alternatively you can do this using Excel text functions without these limitations.
Text to Columns
For example, you have a list of staff names in an Excel spreadsheet. The list is in a column of data and each cell contains the first name AND surname, separated by a space. To use Text to Columns, select the cells containing the names, then choose Text to Columns.
In Excel 2003 choose Data, Text to Columns.
In Excel 2007/2010 choose the Data tab, then Text to Columns. In the Text to Columns panel you’ll see the text previewed. Click Next and then choose the Space separator. Note you can select different separators such as commas, tabs or characters. You’ll see that as you choose the Space separator, Excel previews the data now correctly separated. Then click Next and then Finish to complete. Excel replaces the original cells with two columns, one containing the first name and the other containing the surname.
However there are limitations to using Text to Columns.
- You can only use this feature one column at a time, and run the wizard each time.
- You cannot change where the data ends up – the original data is always replaced.
- Excel will overwrite any data in the column to the immediate right of the original data (following a prompt) because it needs this for the surname, in our example.
Using Excel Text Functions
You can achieve the same results, keep the original data, place the resulting data where you want and not overwrite any existing data by using combinations of the four Excel text functions, Find, Left, Right, and LEN. We’ll start with the Find function.
The find function is used to find the position of one or more characters within a piece of text. In our example we want to find the position of the blank space in the original data. In Excel we represent the blank by a space surrounded by quote marks.
Suppose the staff names list starts in cell D1. Cell D1 contains the label “Staff”. The actual data is in cells D2, D3, D4 and so on. You might like to type in “FIND” as a label in cell E1. Then in cell E2 type =FIND(” “,D2) and press Enter. Cell E2 now shows a 5, meaning the space is the fifth character from the left in the original data. You can then fill the formula down all the cells to show the blank space position for all the data. Next we’ll use the Left function, so type “LEFT” as a label in cell F1 and select cell F2.
The Left function will select a specific number of characters from the left hand side of data in a cell. So in cell F2 type =LEFT(D2,E2-1) where D2 is the original data and E2 is the FIND value. After you press Enter the cell will show the first name only. Note we add a minus 1 to the character count so we only show the first name without the space. Then fill this formula down the table as before, and all the first names will show.
The LEN function is used to help with the character count as we prepare to select the surname from the right hand side of each item of data. So we’ll add the label “LEN” in cell G1. Then in cell G2 we type =LEN(D2) where D2 is the original data. After you press Enter the cell shows the total number of characters in the original data. Then fill the formula down as before, all the character counts will show. Now we’re nearly there. Lastly we’ll use the Right function, so we’ll add the label “RIGHT” in cell H1.
The Right function will select a specific number of characters from the right hand side of data in a cell. We’ll use the LEN value minus the FIND value to determine how many characters to select from the right. So in cell H2 type =RIGHT(D2,G2-E2) where D2 is the original data, G2 is the LEN value and E2 is the FIND value. After you press Enter the cell will show the surname only. Then fill down as before to produce a list of all the surnames.
We’ve seen so far how to break up the actions of separating two words separated by a space character into different steps. Once you’re familiar with how to use these functions, we could combine the formula and only show two extra columns to the right of the original data, with one for the first name and the other for the surname. You might like to try this and see how you get on.
Microsoft Excel training is an effective way to increase your skills and knowledge at work. Take a look at how much you can learn at https://www.stl-training.co.uk/microsoft/excel-training-london.php
