Excel 2010 Tip - Splitting Names From One Column To Two Columns

If you have a list of names comprising of a forename and a surname and they are all in one column, you will know that sorting alphabetically on that column would sort by the forename. ie;

Andrew Johnson

Brian Brown

Carol Anderson

It is more likely that you would wish to sort this list in terms of the surname.  ie;

Carol Anderson

Brian Brown

Andrew Johnson

The easy way to do this would be separate the forename and surname into two columns (As a Microsoft Access database developer, I know that it is always a good idea to to separate names into different fields and, likewise in Excel, it is a good idea to put names into different columns).  The advantage of doing this is that you can then perform a sort firstly by surname and then by forename.  All very logical but, if you are faced with one column in Excel which contains both the surname and the forename, how can you easily turn them into two columns without a lot of tedious typing?

There are (probably) a number of different methods which could be employed.  My favourite is to make use of some of Excel's built-in text functions namely;

• Left(Str$,n) - Return a number of characters starting from the left
• Right(Str$,n) - Return a number of characters starting from the right
• Len(Str$) - Return the number of characters in a string (includes spaces)
• Find(Char$,Str$,n) - Finds the position of a specified character, Char$, from Str$ starting at the position n

In a nutshell, these text functions will do the following assuming Str$ = "Carol Anderson";

Left(Str$,3)  - returns "Car"

Right(Str$,3) - returns "son"

Len(Str$) - returns 14 (A 'space' is counted as a character)

Find("A",Str$,1) - returns 7 (Although there is an 'a' before position 7, it is not a uppercase 'A')

You can use the above functions to separate your list of firstnames and surnames into two columns using the following (Assuming the name to be split is in column A, row 1 and you wish to put the firstname in column B and the surname in column C);

In column B (firstname) enter the function;

• =LEFT(A1,FIND(" ",A1,1))

In column C (surname) enter the function;

• =RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

I shall leave you to sort out exactly how it works!

OK - there are limitations here (beware of extra spaces etc) but you might just find it useful.