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.
Get in touch
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.
