If you need extract the last name from a full name, you can do so with this rather complex formula that uses several functions. In the generic form of the formula (above), name is a full name, with a space separating the first name from other parts of the name.
In the example, the active cell contains this formula:
At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters need to be extracted.
At a high level, the formula replaces the last space in the name with an asterisk "*" and then uses FIND to determine the position of the asterisk in the name. The position is used to work out how many characters to extract with RIGHT.
How does the function replace only the last space? This is the clever part.
Which does the actual replacement of the last space with "*".
SUBSTITUTE has a forth (optional) argument that specifies which "instance" of the find text should be replaced. If nothing is supplied for this argument, all instances are replaced. However, if, say the number 2 is supplied, only the second instance is replaced. In the snippet above, instance is calculated using the second SUBSTITUTE:
Here, the length of the name without any spaces is subtracted from the actual length of the name. If there's only one space in the name, it produces 1. If there are two spaces, it the result is 2, and so on.
In the example name in B4, there are two spaces in the name, so we get:
If you need extract the first name from a full name, you can easily do so with the FIND and LEFT functions. In the generic form of the formula (above), name is a full name, with a space separating the first name from other parts of the name. In the...
If you need to get the middle name from a full name, and you already have the first and last names in separate cells, you can use a formula that extracts the middle name using the MID and LEN functions, with help from TRIM function. Note: this is a...
To extract the first name from a full name in "Last, First" format, you can use a formula that uses RIGHT, LEN and FIND functions. In the generic form of the formula (above), name is a full name in this format: LAST, FIRST Jones, Sarah Smith, Jim...
If you need extract the last name from a full name in LAST, FIRST format, you can do so with a formula that uses the LEFT and FIND functions. The formula works with names in this format, where a comma and space separate the last name from the first...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.