Get the last name from a full name quickly using TextRight and the Substring functions, or use an advanced Excel formula.
This post explains everything you need to know about the most important TEXT functions – or string manipulation functions, as it’s known. Then, if you are using custom user-defined functions (see the second part of the article), it’s possible to achieve your goal fast.
Generic formula to get the last name from a name
Here is the formula that contains built-in Excel functions only:
=RIGHT(cell,LEN(cell)-FIND("?",SUBSTITUTE(cell," ","?",LEN(cell)-LEN(SUBSTITUTE(cell," ","")))))
Let us see what you can do if you have a cell and want to get the last name from a full name. At first glance, we have to combine several functions — bad news. But we will explain the method clearly and simply based on the previously mentioned generic formula example.
By default, in cell B3, we find the full text. The last name is separated from the other parts of the full text with a delimiter, in this case, space.
Place this below formula in cell D3:
=RIGHT(B3,LEN(B3)-FIND("?",SUBSTITUTE(B3," ","?",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))))
In a nutshell, the RIGHT function gets the characters starting from the right side of the string. The inner section of the complex formula serves only one purpose: to calculate how many characters need to be extracted.
You can download the practice file that contains all demonstrated formulas and functions.
The formula replaces the last space with a question mark “?”; you can use another asterisk like “*”. The FIND function gets the position of the question mark. The RIGHT function will use this parameter.
=SUBSTITUTE(B3," ","?",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))
In the formula above, an occurrence is calculated using the second SUBSTITUTE.
Using the LEN function, we’ll get the length of the string in cell B3 as a result.
In the example string in B3, we have two spaces in the full name, so we get:
=10 - 8 = 2
We use this value as in the occurrence number:
Which replaces the second space with “?”.
The name then looks like this: “John W?Do” The FIND function then takes over to figure out where the “?” is in the name:
The result is 7 because the “?” is in the 7th position, subtracted from the total length of the text string.
Because the length of the name contains 10 characters:
=10-7 = 3
The RIGHT function uses the 3 as an argument, so we’ll get the final result:
=RIGHT(B3,3) = "Doe"
Find and get the last name using User-Defined Functions
It’s not necessary to go at this paper hammer and tongs. We want to save time and use the fastest method. Check our free Excel add-in! DataFX – our advanced UDF library – provides unique functions for Excel. We continuously integrate the latest string manipulation functions.
Extract the third string of a name: the TextRight function
In the example, use the formula below:
Using the SubString and ExtractNthWord functions
The Substring function gets the nth element of a text string. The elements are separated by a specified separator character (delimiter). That is what is needed if we want to save time.
In the example, we have the full name in cell B3. Apply the formula below because we need to extract the third string, in this case, the last name.
=SubString(B3," ", 3)
The ExtractNthWord user-defined function works similarly to the Substring function. Alternatively, you can use this function in cell D4.
In the example, you want to extract the third word from a text string, use “3” as a second function argument.
We hope that you enjoyed this tutorial. Stay tuned.