How to Extract First Names from Last Names in Excel Spreadsheets
If you are creating a Mail Merge using an Excel spreadsheet, this trick may help you.
Often when downloading your data from your Client Relationship Management software or similar into a spreadsheet ready for a Mail Merge, you will find that both the first and last name are in the same column. In this blog, Henry explains how you can easily extract this information into two columns.
I seem to be sending a lot of merged emails lately and the source file for those emails generally contains the full name and the email, but not the first name.
This is tricky as it is always nice to be able to say Hi Rosie, rather than just Hi or Hi Rosie Shepherd. And you can, because extracting the first name is fairly easy:
Find the first space
Step 1 is to find where the first space occurs, as this will normally be the end of the first name. Use the FIND function, which finds a character – or set of characters - in a cell, for this. This example finds the first space in A2:
You can use the same technique for extracting first names from email, if the email format consists of firstname.surname@. In that case you search for the first full stop.
Use the first space to find the first name
You can then use the LEFT function, which takes the left most characters in a cell. Assuming here that the @FIND function has placed the position of the first space in C2.
It is -1 because you don’t want to include the space itself. You can combine these two into one function like this:
What if some people have only entered a first name?
If there is no space because they have only entered their first name, the above function will result in #VALUE! appearing. This can be fixed using the ISERROR function, which allows you to specify what happens if this function results in an error.
We are assuming that the error results from there being only one word (the first name) in the name cell. So:
- If there is an error, we want that word (the first name) displayed.
- If there is no error, we want the function above to extract the first name.
This results in this function:
=IF(ISERROR(FIND(" ",A2)),A2,LEFT(A2,FIND(" ",A2)-1))
If looking for a space in A2 results in an error, display A2. If there is a space, take all the characters up to the space.
This function will work, as long as nobody has put Mr or Professor or similar in front of their name. And it also relies on the first name coming first, which isn’t true in all cultures.
Henry Stewart, Founder and Chief Happiness Officer
Henry is founder and Chief Happiness Officer of Happy Ltd, originally set up as Happy Computers in 1987. Inspired by Ricardo Semler’s book Maverick, he has built a company which has won multiple awards for some of the best customer service in the country and being one of the UK’s best places to work.
Henry was listed in the Guru Radar of the Thinkers 50 list of the most influential management thinkers in the world. "He is one of the thinkers who we believe will shape the future of business," explained list compiler Stuart Crainer.
His first book, Relax, was published in 2009. His second book, the Happy Manifesto, was published in 2013 and was short-listed for Business Book of the Year.