Part 3 of our Excel Guides series. Read Part 1, Part 2 and Part 4 here.
Excel is extremely powerful for manipulating data and cleaning up and merging lists.
Most lists have the contacts name in a format that you don’t want.
This example shows one way to fix this: Splitting one name field into First name and Surname
Step 1
First, set up an Excel file as shown below:
Step 2
Next, you will need to select the field below “First Name” and type in the following formula: =LEFT(A2,SEARCH(" ",A2)-1) . You may need to change the field references depending on the layout of your spreadsheet.
This formula searches the targeted field for a space, it then takes all of the information from the left of the space (removes the space) and displays that information.
Step 3
You will now need to select the field below “Surname” and type in the following formula: =RIGHT(A2,LEN(A2)-SEARCH(" ",A2)) . Again, you made need to change the field references depending on the layout of your spreadsheet.
This formula searches the targeted field for a space, it then takes all of the information from the right of the space and displays that information.
Step 4
If you would like this to apply to a long list, then you can simply drag down from the first field containing the formula, and it will be applied to all selected. The same applies to the surname column, simply drag down and the formula will apply itself.
You can substitute other characters for the space such as commas or a text string. You can also nest the formulas into or commands to do two jobs at the same time.
Have fun!