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.


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," ",""))))))


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

The result returns the last word "Word".


Step 1: To count the number of spaces

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

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

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

Step 3: Find the location of the special character

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

Step 4: The number of the letters after special character

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

Step 5: Pick up the letters after the special character

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

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.

Download Example

Leave a Reply