Extracting Specific Text from a Cell

[nymyth]

Hi all,

How can I pull out specific text from a cell, for example if I have the following

Budweiser 12pk Bottle 12oz
Yeungling 6pk Can 12oz
Heineken Keg 13.2gal
Stella 12pk Bottle 18oz

in the column after this I want to be able to extract whether it is a can, bottle or keg.

Thanks

 

[Aladin Akyurek]

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&{"bottle","can","keg"}&" "," "&A2&" "),{"bottle","can","keg"})

 

[nymyth]

you guys are amazing...thanks a lot

 

[Joe4]

Here is one way:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),500),250))

 

[Aladin Akyurek]

Glad to help.