If you work with Excel long enough, you'll eventually come across text of varying length, and you'll want to know how to extract only the last word, no matter the length of the text. While Excel does not have a built-in function to extract the last word from text, we can combine different Excel functions to write a formula that retrieves the last word only. We'll walk through the process to write this formula step by step, but you can skip to the end and copy the full formula if you're in a hurry!
Note that this article is about extracting the last word from text in Excel; we also have an article about extracting the first word from text, which requires a different approach.
Say, for example, that you have this spreadsheet with the names of former presidents. You want to extract only the last name of each president with a single Excel formula.
We can break down this formula into multiple steps, first by identifying the functions we need to use, and then combining them into a single formula.
We'll start with the RIGHT function. The RIGHT function in Excel returns a specified number of the right-most characters from a string of text. We need to use this function because the last word in a string of text will be the right-most characters.
=RIGHT(text, [number_of_characters])The text argument is the text we wish to extract the right-most characters from. We can type text directly within the parentheses (in quotes), or we can enter a cell reference to a cell which contains text. This argument is required.
The number_of_characters argument is optional (though we will be using it) and specifies how many characters to return from the right side of the text. If you use 5 for this argument, the formula will return the last five characters from the text. Without this argument, the function will simply return the single right-most character from the text.
To try this, double-click into cell D2 and type the following formula:
The formula should return the result "Trump."
So this formula works to extract the last word, but only if the last word is five characters long! This formula will not work for Clinton or for either Bush. The formula would return "inton" for Clinton and would return " Bush" (with the leading space) for George W. and George H. W. Bush. How can we determine what to use for the number_of_characters argument so that Excel extracts only the last word from the text?
We can start with the FIND function.
The FIND function in Excel returns the position of the character or text you are searching for within a longer string of text. In order to extract the last word from text, we need to know where the spaces are, because spaces separate words!
=FIND(search_for, text)The search_for argument is the character or shorter string (substring) of text that you are looking for. In our example it would be a space.
The text argument is the longer string of text that we are searching through to find the search_for argument.
So to find the space in "Bill Clinton" you can double-click into cell D5 in the spreadsheet example above and type the following formula:
Excel will return a value of 5 because the space is the 5th character in "Bill Clinton."
Now we need to find out how many characters we need the RIGHT function to return, that is, how long is the last word that you're trying to extract from text?
To do this, we'll also need to use the LEN function.
The LEN function in Excel returns the length of a string of text, in number of characters. It works like this:
=LEN(text)The text argument is the text you want the length of. So, the length of the final word is the length of the overall text minus the position of the space. To find the length of "Obama" in "Barack Obama" you would use the formula below:
Now we know we can use the RIGHT function to extract the farthest right characters from text in Excel, and we can use a combination of the LEN and FIND functions to determine how long the last word in the text is. We can combine the functions to extract the last word from "Bill Clinton" using the formula below. Double-click into cell D5 in the spreadsheet example above and type the following:
This formula successfully returns "Clinton" and can be copied to other cells as well. However, you might notice that the formula also returns "W. Bush" and "H. W. Bush" because the FIND function is picking up the first space it finds and returning all text to the right of the first space character. In other words, this formula works if there is only one space in the text, as it will return everything to the right of the first space.
But if we wish to extract the last word from text when the text is of variable length, we need to return everything to the right of the final space, not the first space.
To find the final space in order to extract the last word from text in Excel we need to introduce another function. The SUBSTITUTE function in Excel does just what you would guess - it substitutes some text for other text. It works like this:
=SUBSTITUTE(text, old_text, new_text, [nth_appearance])The text argument is required, and this is the text we will search for the old_text argument to replace.
The old_text argument is required and is the text we wish to replace.
The new_text argument is also required and is the text we wish to replace old_text with.
The nth_appearance argument is optional and dictates which old_text we wish to replace if it shows up within the text more than once.
To find the total number of spaces, we can take the total length of the text and subtract the length of the same text without spaces. How might we do that? With the following formula:
This formula says (length of text in cell A2) minus (length of text in cell A2 if we were to substitute the spaces with nothing).
Once we know how many spaces there are, we want to replace it with something we can find later:
This formula will replace the last space with the text "REPLACE ME" (of course, if for some odd reason your text actually contains the words "REPLACE ME" then you should pick something else).
Try copying this formula into the Excel spreadsheet above - it will return the position of the last space in any text contained in the cell it is referencing. We're almost done!
Now we just need to return the rightmost characters from the final space to the end of the text!
Now that we know the RIGHT function and can get the position of the last space in any text, we just need the rightmost characters of length (total length) minus (position of last space).
Hopefully you're still with me! You can copy this formula and double-click into the Excel spreadsheet above, and this formula will return the last word of any text.
There's only one problem - if you reference a cell that contains text without any spaces you get an error because the FIND function is looking for a space. There's one final step to fix this.
The IFERROR function in Excel lets us write a formula, and then returns a specified value if our formula results in an error. It looks like this:
=IFERROR(try_this, do_this_if_error)The try_this argument is the formula you want to try, and the do_this_if_error is what the function returns if try_this errors out.
If our formula above gets an error because the text it's referencing does not contain a space, then that means it is only one word, which means it is also the last word! So, we want to try our formula on the text in a certain cell, and just return the text in that cell if it does not contain a space.
Now we've dealt with text of variable length and know how to handle errors. Let's put our formula inside an IFERROR function so that it works with any text.
Below is the error-proof way to extract the last word from text in Excel, where A2 is the cell which contains the text you are extracting the last word from:
=IFERROR(RIGHT(A2, (LEN(A2) - FIND("REPLACE ME", SUBSTITUTE(A2," ","REPLACE ME",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))), A2)This formula tells Excel to return the rightmost characters of any text from the last space until the end of the text. This formula is long and tedious, but the best way to understand it is to play around with it in a real spreadsheet and get your hands dirty with some practice exercises.
