Extract last two words from cell

Extract last two words from cell

Generic formula 

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,100)

Related formulas 

Get last name from name

Extract nth word from text string

Find nth occurrence of character

Position of 2nd 3rd etc instance of character

Explanation

To extract the last two words from a cell or text string, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. In the example shown, the formula in C5 is:

=MID(B5,FIND("@",SUBSTITUTE(B5," ","@",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1))+1,100)

How this formula works

At the core, this formula uses the MID function to extract characters starting at the second to the last space. The MID function takes just 3 arguments: the text to work with, the starting position, and the number of characters to extract.

In the example shown, the text comes from column B, and the number of characters can be any large number that will ensure that all of the last two words are extracted. The challenge is the starting position, which should be just after the second to last space. The complexity of this formula arises from the steps needed to figure out the starting position.

The clever work is done primarily with the SUBSTITUTE function, which has an optional argument called instance number. This feature is used to replace the second to last space in the text with the "@" character, which is then located with the FIND function. 

Working from the inside out, this snippet of code figures out how many spaces are in the text total, from which 1 is subtracted.

LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1

In the example shown, there are 5 spaces in the text, so the code above returns 4. This number is fed into the outer SUBSTITUTE function as instance number:

SUBSTITUTE(B5," ","@",4)

This causes SUBSTITUTE to replace the forth space character with the at symbol (@). 

Note: the choice of @ is arbitrary. You can use any character that will not appear in the original text.

Next, the FIND function is used to locate the "@" character in the text:

FIND("@","A stitch in time@saves nine")

The result of FIND is 17, to which 1 is added to get 18, which goes into the MID function as the starting position to begin extracting text.

For the number of characters to extract, we have hard-coded 100 to ensure the entire last two words are extracted. This number is arbitrary and can be adjusted to fit the situation.

Extract last two words from cell

Generic formula 

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,100)

Related formulas 

Get last name from name

Extract nth word from text string

Find nth occurrence of character

Position of 2nd 3rd etc instance of character

Explanation

To extract the last two words from a cell or text string, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. In the example shown, the formula in C5 is:

=MID(B5,FIND("@",SUBSTITUTE(B5," ","@",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1))+1,100)

How this formula works

At the core, this formula uses the MID function to extract characters starting at the second to the last space. The MID function takes just 3 arguments: the text to work with, the starting position, and the number of characters to extract.

In the example shown, the text comes from column B, and the number of characters can be any large number that will ensure that all of the last two words are extracted. The challenge is the starting position, which should be just after the second to last space. The complexity of this formula arises from the steps needed to figure out the starting position.

The clever work is done primarily with the SUBSTITUTE function, which has an optional argument called instance number. This feature is used to replace the second to last space in the text with the "@" character, which is then located with the FIND function. 

Working from the inside out, this snippet of code figures out how many spaces are in the text total, from which 1 is subtracted.

LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1

In the example shown, there are 5 spaces in the text, so the code above returns 4. This number is fed into the outer SUBSTITUTE function as instance number:

SUBSTITUTE(B5," ","@",4)

This causes SUBSTITUTE to replace the forth space character with the at symbol (@). 

Note: the choice of @ is arbitrary. You can use any character that will not appear in the original text.

Next, the FIND function is used to locate the "@" character in the text:

FIND("@","A stitch in time@saves nine")

The result of FIND is 17, to which 1 is added to get 18, which goes into the MID function as the starting position to begin extracting text.

For the number of characters to extract, we have hard-coded 100 to ensure the entire last two words are extracted. This number is arbitrary and can be adjusted to fit the situation.