Extract Last Two Words in Excel: Formula
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
To extract the last two words from a cell in Excel, use the formula =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200)). This replaces each space with 100 spaces, then takes the rightmost 200 characters and trims them, leaving exactly the last two words.
Formula
The formula works in three steps:
SUBSTITUTE(A1," ",REPT(" ",100))— replaces every single space in the cell with 100 spaces.RIGHT(...,200)— takes the rightmost 200 characters. Since each space is now 100 characters wide, 200 characters captures the last two words plus the 100 spaces before them.TRIM(...)— removes all extra spaces, leaving only the two words separated by a single space.
For a cell containing apple banana cherry, the result is banana cherry.
Worked Example
Take the default text apple banana cherry in cell A1.
- Replace each space with 100 spaces:
apple ... banana ... cherry(100 spaces between each word). - Take the rightmost 200 characters: that includes the 100 spaces before
banana, thenbanana, then 100 spaces, thencherry— but the rightmost 200 characters actually ends mid-word if the words are long. The 100-space trick ensures that even with long words, the rightmost 200 characters will include the last two complete words. TRIMremoves all leading/trailing/multiple spaces, givingbanana cherry.
For the default, the result is banana cherry.
Why Not Use FIND or LEFT?
Excel has no built-in “last word” function. The standard approach uses FIND and LEFT for the first word, but finding the second-to-last word requires knowing where the second-to-last space is. That position varies with word lengths, so a fixed character count is unreliable. The 100-space trick sidesteps this by making every space wide enough that the rightmost 200 characters always contain the last two words, regardless of how long they are.
How to Adapt for Different Word Counts
- Last word only:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)) - Last three words:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),300)) - Last N words: Replace
200withN*100and theREPTcount stays 100.
This works because each space becomes 100 characters, so taking N*100 characters from the right captures N words plus the spaces before them.
Common Mistakes and Edge Cases
- Single word: If the cell has only one word, the formula returns that word, not an error.
- Multiple spaces between words: The formula treats consecutive spaces as one because
SUBSTITUTEreplaces each space individually, butTRIMcollapses them. However, if there are leading/trailing spaces,SUBSTITUTEwill create 100 spaces at the start/end, andTRIMremoves them — fine. - Words longer than 100 characters: Extremely rare, but if a single word exceeds 100 characters, the rightmost 200 characters might cut into the word. Increase the
REPTcount to 200 or more to be safe. - Non-breaking spaces: If the text uses non-breaking spaces (CHAR(160)),
SUBSTITUTEwith a regular space won’t catch them. Replace" "withCHAR(160)or useTRIMafter converting.
Alternative Approaches
For Excel 365 and Excel 2021, you can use the TEXTSPLIT function:
=TEXTJOIN(" ",TRUE,TAKE(TEXTSPLIT(A1," "),,-2))
This splits the text into an array of words, takes the last two with TAKE, and rejoins them. It’s more readable but not available in older versions. Another option is a VBA user-defined function, but that requires enabling macros and isn’t portable.
Frequently Asked Questions
Can I extract the last two words without a formula?
Yes, you can use Excel’s Flash Fill (Ctrl+E). Type the last two words manually in the adjacent cell, then press Ctrl+E. Excel detects the pattern and fills the rest. This works well for one-off tasks but isn’t dynamic — if the source changes, the result won’t update.
What if my text has punctuation or numbers?
The formula treats any character that isn’t a space as part of a word. So Hello, world! returns world! as the last word. If you want to exclude punctuation, you’d need a more complex formula or Power Query.
Does this work in Google Sheets?
Yes, the same formula works in Google Sheets. The SUBSTITUTE, RIGHT, and TRIM functions are identical.
Why does the formula use 100 spaces specifically?
100 is arbitrary but safe. It must be larger than the longest word you expect. If a word could be 200 characters, use 200 or 500. The larger the number, the more memory used, but for typical text it’s negligible.
Can I extract the last two words from every cell in a column?
Yes, just drag the formula down. It will work for each row independently.
What if the cell is empty?
The formula returns an empty string. If you want a custom message, wrap it in IF(A1="","",...).
Related
Formula used
in t | Text | - | "apple banana cherry" | 0
out two | Last two words | - | TRIM(RIGHT(SUBSTITUTE(t," ",REPT(" ",100)),200)) | 0Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.