# How to Extract the Last Word

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

Formula:

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

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

Example:

To extract the last word from the text string "How to Extract the Last Word".

The result returns the last word "Word".

Explanations:

Step 1: To count the number of spaces

Step 2: Replace the 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 no space, the formula "=FIND("/",SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))" returns #VALUE! error. To avoid this, you need to combine with IF Function.

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

=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 is only one word.