[no_toc] Get the last name from a full name quickly using TextRight and Substring function! Or – if you have time enough – use a complex formula that uses several functions.
This post explains everything you need to know about the most important TEXT functions – or string manipulation functions, as it’s also known. If you are using custom user-defined functions (see the second part of the article), it’s possible to achieve your goal fast.
Nevertheless, contrary to all expectations, we’ll start with examples based on regular excel functions.
Generic formula to get the last name from a name
=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 you want to get the last name from a full name. At first glance, we have to combine several functions — bad news.
But we’ll try to explain the method clearly and simply. We’ll go on based on the previously mentioned generic formula example.
By default, in cell A2, 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 B2:
=RIGHT(A2,LEN(A2)-FIND("?",SUBSTITUTE(A2," ","?",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
Composition of Formula
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: calculate how many characters need to be extracted.
The formula replaces the last space with a question mark “?”, you can use another asterisk like “*”. The FIND formula gets the position of the question mark.
The RIGHT function will use this parameter.
SUBSTITUTE(A2," ","?",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))
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 A2 as a result. In the example string in A2, we have two spaces in the full name, so we get:
=10 - 8 = 2
We’ll 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:
FIND("?", "John W Doe")
The result is 7 because the “?” is in the 7th position, which is subtracted from the total length of the string.
Because the length of the name is 10 characters:
=10-7 = 3
The RIGHT function uses the 3 as a parameter, so we’ll get the final result:
The Power of User-Defined Functions
Think a little bit! It’s not necessary to go at this paper hammer and tongs. We want to save our time and using the fastest method. Check our free excel add-in!
DataFX – our advanced UDF library – provides a unique functions for Excel. We’ll keep coming back week after week with the latest string manipulation functions.
Let’s see how it works!
Extract the last name using 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 our time.
In the example, we have the full name in cell A5. Apply the formula below, because we need to extract the third string, in this case, the last name.
The ExtractNthWord function works similarly to the Substring function.
We’ll use this function for the cell A4.