Get last name from name

Get last name from name in Excel - formula examples

You can quickly get the last name from a full name using the RIGHT, FIND, LEN, and SUBSTITUTE functions or 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), achieving your goal is possible.

Steps to get the last name from a name

Use the following steps to extract the first name in Excel:

  1. Open Excel
  2. Type =RIGHT(B3,LEN(B3)-FIND(“?”,SUBSTITUTE(B3,” “,”?”,LEN(B3)-LEN(SUBSTITUTE(B3,” “,””)))))
  3. The formula will extract the first name from cell B3.

Explanation

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. However, we will explain the method clearly and 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.

explanation-of-the-formula

Place this below formula in cell D3:

=RIGHT(B3,LEN(B3)-FIND(“?”,SUBSTITUTE(B3,” “,”?”,LEN(B3)-LEN(SUBSTITUTE(B3,” “,””)))))

In summary, 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 the number of characters that need to be extracted.

  1. 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.
  2. In the formula above, an occurrence is calculated using the second SUBSTITUTE.
  3. We’ll get the string length in cell B3 using the LEN function.
  4. We have two spaces in the full name, so we get =10 – 8 = 2
  5. Use this value as in the occurrence number: =SUBSTITUTE(B3,”, “”?”,2), which replaces the second space with “?”
  6. The name then looks like this: “John W?Do”. The FIND function then takes over to figure out where the “?” is in the name.
  7. The result is 7 because the “?” is in the 7th position, subtracted from the total length of the text string: =LEN(B3)-7
  8. The RIGHT function uses the 3 as an argument, so we’ll get the final result: =RIGHT(B3,3) = “Doe”

Extract the last name using the GETWORDS function

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.

get last name using udf

In the example, use the formula below:

=GETWORDS(B3, 3, ” “)

The substring function gets the nth element of a text string. The elements are separated by a specified separator character (delimiter) to save time.