Extract second to last word in a cell

[Mychaltb]

All,

Anyone know how to extract the second to last word of a cell using a formula? I am trying to find a way to extract the country name (second to last word of each cell) or have a formula lookup the country in the cell and return a number value based on lookup table. I have not been able to find my answer online so if anyone here can help it would be greatly appreciated!

FYI, I have about 2,000 lines ranging from 20 different countries. The second to last word is the name of the country in all cells. I know I could just do text to columns but not all the cells have the same amount of words so I would rather have a formula spit out what I need.

Example:

Starting in cell A1: Beer Pale Ale England Bottle
Return country name in cell b1: England

Thanks!
Mychal

 

[taxcel rose]

Hi,

try this:
=LEFT(MID(A1,FIND("##",SUBSTITUTE(A1," ","##";LEN(A1)-LEN(SUBSTITUTE(A1," ",))-1))+1,999),FIND(" ",MID(A1,FIND("##",SUBSTITUTE(A1," ","##",LEN(A1)-LEN(SUBSTITUTE(A1," ",))-1))+1,999))-1)

 

[Scott T]

Try where A1 has the text string

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",999)),1998),999))

 

[AlanY]

try

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))

 

[palaeontology]

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)-2)*LEN(A1)+1, LEN(A1)))

Kind regards,

Chris

 

[Mychaltb]

Thanks Chris! This worked! I really appreciate your help!