How to Extract First Names from Last Names in Excel Spreadsheets

In: BlogDate: Nov 02, 2020By: Henry Stewart

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: 

=FIND(“ “,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.  

=LEFT(A2,C2-1)  

It is -1 because you don’t want to include the space itself. You can combine these two into one function like this: 

=LEFT(A2,FIND(“ “,A2)-1) 

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. 

Improve your productivity in Excel with happy

Happy has high-quality, learner-focused Excel training courses for all skill levels, designed to improve your confidence and productivity. And our new online learning programme is just as interactive as our classroom sessions.

Here are our most popular options:

Our learners tell us that they save an average of 32 minutes a day with our Excel training courses. How much time could you save with us?

Why learn online with Happy?

  • Interactive and engaging - just like our classroom sessions
  • Bitesize or full-day sessions - fit around your schedule
  • Learn from home - all you need is a quiet place to call from and an internet connection
  • IT Helpline - 2 years of free support after your course
  • No quibble money-back guarantee

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.

You can find Henry on LinkedIn and follow @happyhenry on Twitter.

More by Henry