How to Extract the Last N Words

To extract the last N words in the text string, you need to find the relative location of the Nth last space, replace with a special character to distinguish with other spaces, then use Right Function.


Copy the formula and replace "A1" with the cell name that contains the text you would like to extract, and change N to the number you need.

=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<N, A1, RIGHT(A1,LEN(A1)-FIND("/", SUBSTITUTE(A1," ","/", (LEN(A1)-LEN(SUBSTITUTE(A1," ",""))- N+1)))))


To extract the last five words from the text string "How to Extract the Last N Words".

The result returns the last five words "Extract the Last N Words".


Step 1: To count the number of spaces

Formula=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

Step 2: Replace the fifth space from the last with any special character (e.g., /)

Formula=SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-5+1))
ResultHow to/Extract the Last N Words

Step 3: Find the location of the special character

Formula=FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-5+1)))

Step 4: The number of the letters after special character

Formula=LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-5+1)))

Step 5: Pick up the letters after the special character

Formula=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-5+1))))
ResultExtract the Last N Words

However, when text string has less than 5 words, the formula "=FIND("/",SUBSTITUTE(A1," ","/", (LEN(A1) - LEN(SUBSTITUTE(A1," ",""))-5+1)))" returns #VALUE! error. To avoid this, you need to combine with the IFERROR function.

=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<5, A1, RIGHT(A1, LEN(A1)- FIND("/", SUBSTITUTE(A1," ","/", (LEN(A1)- LEN(SUBSTITUTE(A1," ",""))-5+1)))))

One Response

  1. Alex March 16, 2022

Leave a Reply