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.
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," ","")))<4, A1, RIGHT(A1, LEN(A1)-FIND("/", SUBSTITUTE(A1," ","/", (LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)))))
Example:
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".
Explanations:
Step 1: To count the number of spaces
| Formula | =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) |
|---|---|
| Result | 6 |
Step 2: Replace the fourth last space with any special character (e.g., /)
| Formula | =SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)) |
|---|---|
| Result | How 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))) |
|---|---|
| Result | 15 |
Step 4: The number of the letters after special character
| Formula | =LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3))) |
|---|---|
| Result | 19 |
Step 5: Pick up the letters after the special character
| Formula | =RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)))) |
|---|---|
| Result | the 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.
