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.

Excel 2010 - More than you think

Over the last week I have been delivering Excel 2010 Intermediate training for a local training company based near Telford.  Although I have been using Excel 2010 since it was introduced, it always amazes me that, every time I deliver some training, I find more and more useful features, short-cuts etc. that are new to me.  It is very easy to think that you know a particular software application really well and that there is little else for you to learn - I would beg to differ.  You may be happy using a software application such as Excel 2010 in a particular way but, the chances are, there may be a much more efficient way of performing the same task.  Remember the old adage "You don't know what you don't know".

Training is rarely a 'waste of time' or 'not worth the money'.  Admittedly not all you are told on a training course will 'stick' but my experience shows that you will remember that an application can perform a particular task even if you can't remember the exact process.  Unfortunately, in this time of economic uncertainty or doubt, the training budget is usually one of the first casualties in any cut backs.  This is a shame as this really is a false economy.

QuickFile

Have started using the free, online accounting system 'QuickFile' which is aimed at the small business.  Must admit that I am very impressed with what I have seen so far.  The ability to create clients and give them their own particular control panel where they can:

• See all their invoices
• See PDF versions of invoices
• Send messages to me
• Make payments online (if this has been setup)

Not bad for a free service.