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

- Extract the Last Word
- Extract the Last Three Words
- Extract the Last Four Words
- Extract the Last N Words

**Formula:**

Copy the formula and replace "A1" to the cell name that contains the text you would like to extract.

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

**Example:**

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

The result returns the last two words "**Two Words**".

**Explanations:**

**Step 1:** To count the number of spaces

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

Result | 6 |

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

Formula | =SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)) |
---|---|

Result | How to Extract the Last/Two Words |

**Step 3:** Find the location of the special character

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

Result | 24 |

**Step 4:** The number of the letters after special character

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

Result | 9 |

**Step 5:** Pick up the letters after the special character

Formula | =RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)))) |
---|---|

Result | Two Words |

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

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

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