Home » Our News » Splitting Excel fields

Splitting Excel fields
09 January 2015

Excel is extremely powerful for manipulating data and cleaning up and merging lists. Pretty much every list has the contacts name in a format that is not what you want. Here is one way to put that right. Not meant to be teaching grandma to suck eggs but if it is useful, great.

Splitting one name field into First name and Surname

First, insert two new columns immediately to the right of the column with the full name in and label them "First name" and "Surname"

Place your cursor in the column at the top of the list and type in the formula - =LEFT(A2,FIND(" ",A2)-1) . Change the field references to match your file. This formula finds the blank space between the first and surname - the -1 removes the space from the result putting everything to the left of the space into the new column.

Next place the cursor in the Surname filed and type the following formula - =RIGHT(A2,LEN(A2)-(FIND(" ",A2))) . Again change the field references to match your file. This again finds the blank space and places everything to the right into the surname field.

Copy the two new and Surname fields down your data file so that it calculates the new content for each record. Select the two columns (B and C in the example), copy and Paste Values only into the same columns (accessed through "Paste Special" sub menu on old versions of Excel.

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. For example this formula would find the but ensure it is in Title case in the new field even where the original full name had been type in using only lower case letters.

=PROPER(LEFT(A2,FIND(" ",A2)-1))

Have fun.