In this tutorial, let’s learn how to extract the last name from any given the full name by using a quite tricky and complex formula that contains several different functions. Despite its complexity, the formula is actually very easy to use.
Get the Last Name From Name
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
The name is a full name that contains a blank space between the first name and the last name.
The core of this formula lies in the RIGHT function which is used to extract characters starting from the right. The rest of the function seems to be complex though it only does one job and one job only that calculates how many characters need to be extracted.
To be able to calculate that, the formula creates an asterisk “*” replacing the last space in the name then using FIND function to find exactly the position of the asterisk. From there, the RIGHT function knows where to stop extracting from the right.
Figure 1. Example on how to get the last name from the name
How does this work?
In this example,
Replace name with B5.
Then we have the formula in D5 is:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))
The key to replacing the space with an asterisk is based on this part of the formula:
SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))
The fourth argument of SUBSTITUTE defines which “instance” of the text to be replaced. If it is not supplied, everything will be replaced. In case it is supplied, let say 2, only the second instance will be replaced. In the above section of the formula, the fourth argument is calculated using this bit:
The second SUBSTITUTE function replaces the space in B5 with no space.
SUBSTITUTE(B5," ","") = AbbieSadler
Using the two LEN functions to calculate the number of characters.
Returns 12 since there are 12 characters in the name “Abbie Sadler” and
Take the former subtract the latter and we get the number of space in the name.
12 – 11 = 1
This gives us an idea of how many spaces there are in the name and in this case, there is 1.
Know this and the first SUBSTITUTE function can replace the asterisk with the exact instance.
Replaces the first and only space with “*”. The name then looks something like this:
The FIND function locates the asterisk and returns the position of the asterisk in the name.
The result is 6 because “*” is in the 6th position. It is then subtracted by the length of the name though:
12 – 6 = 6
The number 6 is then used by RIGHT function:
Which results in “Sadler”.
- A common error with this formula is inconsistent spaces. Extra space or no space in the name will cause trouble. I recommend using the TRIM function first to fix the spacing problem before moving on with the formula.
- Understand the logic and now you can extract any last name and even first name by simply replace RIGHT with LEFT.