ThisCalculator

Everyday

Extract Last Two Words in Excel: Formula

Updated · Standard formula, shown below

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.

  1. Replace each space with 100 spaces: apple   ...   banana   ...   cherry (100 spaces between each word).
  2. Take the rightmost 200 characters: that includes the 100 spaces before banana, then banana, then 100 spaces, then cherry — 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.
  3. TRIM removes all leading/trailing/multiple spaces, giving banana 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 200 with N*100 and the REPT count 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 SUBSTITUTE replaces each space individually, but TRIM collapses them. However, if there are leading/trailing spaces, SUBSTITUTE will create 100 spaces at the start/end, and TRIM removes 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 REPT count to 200 or more to be safe.
  • Non-breaking spaces: If the text uses non-breaking spaces (CHAR(160)), SUBSTITUTE with a regular space won’t catch them. Replace " " with CHAR(160) or use TRIM after 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)) | 0

Each 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.