Extract the Last Word From a Cell in Excel

To extract the last word from a cell in Excel, find the last space and take everything after it. In Microsoft 365 or Excel 2024, =TEXTAFTER(A2," ",-1) does this in one step. In older versions, a formula built from RIGHT, SUBSTITUTE and TRIM does the same job. This guide shows both, with the errors you are likely to hit, for anyone splitting last names, cities or product codes out of a text column.

The example data#

All the examples use this list of full names in column A. Some names have two words, some have three or four, so a good formula must work for all of them.

Column B shows the result we want. The "last word" is the text after the last space in the cell.

The quick way: TEXTAFTER with -1#

TEXTAFTER returns the text that comes after a delimiter. A delimiter is the character that separates the parts, here a space. The syntax is:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

The third argument, instance_num, says which space to use. 1 means the first space, 2 the second, and a negative number counts from the end. So -1 means the last space:

=TEXTAFTER(A2," ",-1)

For Maria Lopez Garcia, the result is Garcia. Fill it down (Windows: Ctrl+D; Mac: ⌘D) and every row returns the right last name, whether the name has two words or four.

TEXTAFTER and its partner TEXTBEFORE need Microsoft 365 or Excel 2024. In Excel 2021 or earlier they show #NAME?. The TEXTBEFORE and TEXTAFTER lesson walks through both functions step by step.

The classic way: RIGHT, SUBSTITUTE and TRIM#

In any version of Excel, this formula returns the last word, however many words the cell has:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

It looks strange, but each part has one job:

1. REPT(" ",100) makes a block of 100 spaces.
2. SUBSTITUTE(A2," ",...) replaces every single space in the name with that block of 100 spaces. The words are now far apart.
3. RIGHT(...,100) takes the last 100 characters. Because the words are so far apart, those 100 characters hold only the last word plus a lot of spaces.
4. TRIM(...) removes the extra spaces and leaves Garcia.

The only limit is that the last word must be shorter than 100 characters, which is true for names, cities and most codes. A nice side effect: if the cell has no space at all, the formula returns the whole cell instead of an error. The RIGHT function guide explains the RIGHT part on its own.

Why RIGHT and FIND only work for two words#

Many people first try RIGHT with FIND. FIND returns the position of a character in the text:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

For Tom Baker this works. LEN counts 9 characters, FIND finds the space at position 4, and RIGHT takes the last 9 - 4 = 5 characters: Baker.

For Maria Lopez Garcia it returns Lopez Garcia. FIND always finds the first space, so with three words you get the last two. This is the most common mistake with this task. You can fix it by finding the last space instead. This version counts the spaces, swaps the last one for a ^ sign, then finds the ^:

=RIGHT(A2,LEN(A2)-FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

For Maria Lopez Garcia, there are 2 spaces, so the second space becomes ^ at position 12, and RIGHT takes the last 18 - 12 = 6 characters: Garcia. It works, but the TRIM and REPT formula above is shorter and easier to check. See the FIND function guide for how FIND counts positions, and how FIND and SEARCH locate text for the difference between the two.

Handle cells with one word or extra spaces#

Real data is rarely clean. Three problems come up often.

No space in the cell. If a cell holds just "Cher", there is no space for TEXTAFTER to find, so it returns #N/A. Use the sixth argument, if_not_found, to return the whole cell instead:

=TEXTAFTER(A2," ",-1,,,A2)

The two empty arguments in the middle keep the default match mode and match end.

Trailing spaces. If a cell is "Tom Baker " with a space at the end, the last space comes after Baker, and TEXTAFTER returns an empty result. Clean the text first with TRIM, which removes spaces at the start and end and turns double spaces into single ones:

=TEXTAFTER(TRIM(A2)," ",-1)

Other separators. Product codes often use hyphens, like "LAP-15-SILVER". Change the delimiter: =TEXTAFTER(A2,"-",-1) returns SILVER. TEXTAFTER also accepts several delimiters at once in curly brackets, so =TEXTAFTER(A2,{" ","-"},-1) splits on whichever comes last.

Get everything except the last word#

Sometimes you need the opposite: the first name and middle name, without the last name. Use TEXTBEFORE with -1:

=TEXTBEFORE(A2," ",-1)

For Maria Lopez Garcia, the result is Maria Lopez. In older versions, use LEFT with the position of the last space from the formula above, minus 1.

The same idea works for other splits. To take the part after the @ sign in an email address, see how to extract the domain from an email. To take text from the middle of a cell, such as a code inside brackets, see extracting text between two characters.

When the last word is a number#

If the last word is a number, such as "Order total 450", the formulas above return text that looks like 450. SUM ignores it and comparisons may fail. Wrap the result in VALUE to turn it into a real number:

=VALUE(TEXTAFTER(A2," ",-1))

The result is the number 450, which you can add, average or compare. For numbers buried in the middle of text, the guide on extracting numbers from text covers more patterns.

Flash Fill: the no-formula option#

For a one-off list, Flash Fill can be quicker than any formula. Type Garcia in B2, move to B3, and choose Data > Flash Fill (on Windows the shortcut is Ctrl+E). Excel spots the pattern and fills the rest of the column.

Check the results before you trust them. Flash Fill guesses from your example, and an unusual row can break the pattern. It also writes plain values, so if a name in column A changes later, column B does not update. Use a formula when the list will change or when a test asks for one.

Practise it#

Splitting text is a common task in Excel job tests, and the -1 trick is easy to forget under time pressure. Practise TEXTBEFORE and TEXTAFTER in a live spreadsheet: you type the formula and it checks it instantly. Then try the FIND and SEARCH lesson to build the classic formulas that work in every version, or start with the exercise on this page.