Extracting Specific Text from a Cell

nymyth

Board Regular
Joined
Mar 4, 2010
Messages
104
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,165
In B2 enter and copy down:

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
Here is one way:
Code:
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),500),250))
 

Watch MrExcel Video

Forum statistics

Threads
1,095,359
Messages
5,444,005
Members
405,259
Latest member
charliechaz

This Week's Hot Topics

Top