# How to Extract the Last Three Words

To extract the last three words in the text string, you need to find the relative location of the third 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.

=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<3, A1, RIGHT(A1,LEN(A1)-FIND("/", SUBSTITUTE(A1," ","/", (LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2)))))

Example:

To extract the last two words from the text string "How to Extract the Last Three Words".

The result returns the last three words "Last Three Words".

Explanations:

Step 1: To count the number of spaces

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

Step 3: Find the location of the special character

Step 4: The number of the letters after special character

Step 5: Pick up the letters after the special character

However, when text string has less than 4 words, the formula "=FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2)))" returns #VALUE! error. To avoid this, you need to combine with IF Function.

=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))<3, A1, RIGHT(A1, LEN(A1)-FIND("/", SUBSTITUTE(A1," ","/", (LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2)))))

=SUBSTITUTE(A1," ","") to replace spaces in the text string;

=LEN(SUBSTITUTE(A1," ","")) to count the length of the text string when spaces are replaced;

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) to count the number of spaces in the text string.

Use IF function to return the text string itself when there are three words or less.