How to Extract the Last Four Words

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

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


To extract the last four words from the text string "How to Extract the Last Four Words".

The result returns the last four words "the Last Four Words".


Step 1: To count the number of spaces

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

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

Formula=SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3))
ResultHow to Extract/the Last Four Words

Step 3: Find the location of the special character

Formula=FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)))

Step 4: The number of the letters after special character

Formula=LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)))

Step 5: Pick up the letters after the special character

Formula=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3))))
Resultthe Last Four Words

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

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

=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 four words or less.

Leave a Reply