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.

Formula:

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)))))

Example:

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

Explanations:

Step 1: To count the number of spaces

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

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)))
Result7

Step 4: The number of the letters after special character

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

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)))))

Leave a Reply