Explanation
In this example, the goal is to extract the first name from a list of names in “Last, First” format as seen in column B. There are several ways to approach this problem. In the current version of Excel, the easiest solution is to use the TEXTAFTER function. In older versions of Excel, it can be solved with a more complex formula based on the RIGHT, LEN, and FIND functions. Both approaches are explained below.
Modern solution
In the current version of Excel, the TEXTAFTER function is the best way to solve this problem. TEXTAFTER extracts text that occurs after a given delimiter. In its simplest form, TEXTAFTER only requires two arguments, text and delimiter :
=TEXTAFTER(text,delimiter)
In the worksheet shown, the formula we are using to return the last name looks like this:
=TEXTAFTER(B5,", ")
- text - B5, the name in column B
- delimiter - “, " (a comma followed by a single space)
In this configuration, the TEXTAFTER function simply returns all text that appears after the comma and space. If the space character is not consistent (i.e. sometimes the comma is followed by a space, and sometimes not) you can provide more than one delimiter to TEXTAFTER to handle both situations like this:
=TEXTAFTER(B8,{", ",","})
Legacy solution
In older versions of Excel that do not provide the TEXTAFTER function, you can solve this problem with a more complex formula based on the RIGHT, LEN, and FIND functions:
=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)
As the formula is copied down, it returns the first name from each name in column B. This formula achieves the same result but in a more manual way. At the core, the RIGHT function is used to extract the first name from the full name in column B, starting from the right . The RIGHT function takes two arguments, the text itself and num_chars , which specifies how many characters to extract:
=RIGHT(text,num_chars)
For example, if we use “apple” for text and 3 for num_chars , we get “ple”:
=RIGHT("apple",3) returns "ple"
The complexity in the formula comes from working out how many characters to extract, which is done with the snippet below:
LEN(B5)-FIND(",",B5)-1
The challenge problem is to calculate the length of the first name. To work this out, we locate the position of the comma (”,") in the text, then we subtract this location from the total length of the text:
LEN(B5)-FIND(",",B5)-1
The LEN function calculates the total characters in the text:
LEN(B5) // returns 10
Because there are ten characters in “Chang, Amy”, LEN returns 10. Next, the FIND function is used to locate the comma (",") in the text:
=FIND(",",B5) // returns 6
Because the comma (",") occurs as the sixth character in the text, FIND returns 6. When we subtract 6 from 10, we get 4:
=LEN(B5)-FIND(",",B5)
=10-6
=4
This is close to what we need, but “Amy” contains 3 characters, not 4 characters. If we ask RIGHT for the last 4 characters in “Chang, Amy”, we’ll also get the space that follows the comma. So, we need to subtract 1 to take the comma into account:
=LEN(B5)-FIND(",",B5)-1
=10-6-1
=3
The code above returns this result directly to the RIGHT function as the num_chars argument:
RIGHT("Chang, Amy",3) // returns "Amy"
And RIGHT returns “Amy” as the final result.
Note: If there is no space after the comma, there is no need to subtract 1.
Get the last name
To extract the last name from the names in column B, you can use a similar formula based on the LEFT function :
=LEFT(B5,FIND(",",B5)-1)
See the example here for a full explanation.
Explanation
In this example, the goal is to extract the last name from names that appear in
Note: This is a great example of how new functions in Excel like TEXTBEFORE and TEXTAFTER are game-changers that can radically simplify formulas. Note the contrast between the modern solution and the legacy solution.
Modern solution
In the current version of Excel, the TEXTAFTER function is the best way to solve this problem. TEXTAFTER extracts text that occurs after a given delimiter. In its simplest form, TEXTAFTER only requires two arguments, text and delimiter. However, in this case, we also need to provide an instance number:
=TEXTAFTER(text,delimiter,instance_num)
In the worksheet shown, the formula we are using to return the last name looks like this:
=TEXTAFTER(B5," ",-1)
- text - B5, the full name in column B
- delimiter - " “, a single space
- instance_num - provided as -1, the first space from the end
Legacy solution
In older versions of Excel that do not offer the TEXTAFTER function, you can use an alternative formula that looks like this:
=MID(B5,FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))+1,100)
Note: it is also possible to use the RIGHT function in a similar formula, but using MID is a bit of a shortcut.
This is a complicated formula. One reason it’s complicated is that we don’t have a direct way to find the last space in a name, which is important when a name contains more than two words (i.e. contains one or more middle names). As a result, we need to employ some trickery with the SUBSTITUTE function to locate and mark the last space in the name with an asterisk (*).
At the core, this formula uses the MID function to extract characters in the name starting at a particular location. The complex part of the formula does just one thing: it calculates how many characters need to be extracted, represented by n below:
=MID(B5,n,100)
The code that calculates n is below:
FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))+1
At a high level, the snippet above replaces the last space (” “) in the full name with an asterisk () and then uses the FIND function to determine the numeric position of the asterisk (). Once we have that number, we simply add 1 to determine a start_num for MID.
How does the code replace only the last space with an asterisk? This is the clever part. Buckle up, because the explanation gets a bit technical. The key to this formula is this bit:
SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))
Normally, the SUBSTITUTE function will replace all instances of old_text with new_text . However, SUBSTITUTE has an optional fourth argument called instance_num that specifies which “instance” of the old text should be replaced. If the argument is omitted, all instances are replaced. If a number like 2 is provided, SUBSTITUTE will replace only the second instance.
At this point, the problem becomes how to calculate the correct instance_num . Looking back at the names in column B, we can see that we want to provide instance number as 2 when a name contains a middle name, and instance number as 1 when there is a middle name. The way we solve the problem is to count the number of spaces in the name, which we do in the snippet below:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
This is a fairly common pattern in Excel formulas that must calculate how many times a character appears in a text string. In brief, we calculate the total length of the text string with the LEN function , then subtract the length of the text string after the target character has been removed with SUBSTITUTE. You can find a more detailed explanation here .
In cell B5, the name is “Susan Ann Chang”, so the code above evaluates like this:
=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
=15-LEN("SusanAnnChang")
=15-13
=2
This means we have 2 spaces in the name, and 2 becomes the instance number used to replace the second space with an asterisk (*). Below is the formula simplified to show the calculated 2:
=MID(B5,FIND("*",SUBSTITUTE(B5," ","*",2))+1,100)
After SUBSTITUTE runs, we have “Susan Ann*Chang”:
=MID(B5,FIND("*","Susan Ann*Chang")+1,100)
We’re getting close!
Next, the FIND function runs and returns the numeric position of the asterisk () in “Susan AnnChang”, which is 10. We then add 1 to get a starting position of 11. This is the number used for n in the formula above. The formula calculates a final result like this:
=MID(B5,10+1,100)
=MID(B5,11,100)
="Chang"
The MID function begins extracting text at the 11th character, extracts all remaining text, and returns “Chang” as a final result.
Notice that we provide num_chars as 100. This arbitrary number is part of a shortcut with MID. When num_chars is larger than the remaining characters, the MID function is programmed to simply extract all remaining text , which works perfectly in this case. You can increase this number as needed.
With the RIGHT function
As mentioned above, the RIGHT function can also be used to extract the last names like this:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))
This formula is similar to the formula above, but a bit more complex because we need to be more precise when calculating the number of characters to extract from the RIGHT. With the MID function, once we know the location of the asterisk, we simply ask for all remaining text using an arbitrarily large number. With RIGHT, we need to work out the number of characters to ask for by subtracting the position of the asterisk from the total length of the name.
Note: Extra spaces in the names will cause problems with the formulas on this page. One solution is to use the TRIM function first to normalize spaces, then use the formula on the result from TRIM.
