Site icon ExcelNotes

How to Extract the Last Two Words

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.

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

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

Formula=SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))
ResultHow 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)))
Result24

Step 4: The number of the letters after special character

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

Step 5: Pick up the letters after the special character

Formula=RIGHT(A1,LEN(A1)-FIND("/",SUBSTITUTE(A1," ","/",(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))))
ResultTwo 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.

Download Example

Exit mobile version