Trouble searching text string

[DarenJohnson]

This is driving me moderately insane(!) though I suspect you guys will come up with a solution for this in no time.

I am trying to create an excel formula which will search a column of text strings for one of several text strings. e.g.

A1 = "blah blah blah wskkxp23 blah blah blah"
A2 = "d002dces22 blah blah blah"
A3 = "blah blah blah"
etc

I would like for column B to just extract the part fo the text I'm interested in i.e.

B1 should= "wskkxp23"
B2 should= "D002dcs22"
B3 should= blank/na/anything other than one of the text searched for
etc

The list of things I'm searching for may increase so I'd like to be able to just do this by named range (if possible). Currently I know I need to find:
D001*
D002*
T001*
T002*
WS*

I've tried doing this via vlookup, lookup, match, search, etc but I just cannot get this to work properly.

Many thanks
Daren

 

[mikerickson]

In A2, wouldn't "d002dces22 blah blah blah" match "D002*" as well as "doo2dces22"

 

[DarenJohnson]

Apologies Mike this was a typo. it should read:

B2 should= "D002dces22"

And yes I'd like anything matching d002 to be displayed in column b

 

[Richard Schollar]

Here's a long winded possibility - I'm sure someone will improve on it:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">"blah blah blah wskkxp23 blah blah blah"</td><td style=";">wskkxp23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">D001</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">"d002dces22 blah blah blah"</td><td style=";">d002dces22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">D002</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">"blah blah blah"</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T001</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T002</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">WS</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,LOOKUP(<font color="Red">2^15,SEARCH(<font color="Green">$H$1:$H$5,A1</font>)</font>),FIND(<font color="Red">" ",A1&" ",LOOKUP(<font color="Green">2^15,SEARCH(<font color="Purple">$H$1:$H$5,A1</font>)</font>)</font>)-LOOKUP(<font color="Red">2^15,SEARCH(<font color="Green">$H$1:$H$5,A1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[mikerickson]

If you have a named range, KeyRange with these search terms (no *)
D001
D002
T001
T002
WS

then this CSE formula should work
=TRIM(LEFT(SUBSTITUTE(MID(A1,MIN(SEARCH(KeyRange, A1&KeyRange)), 255), " ", REPT(" ",255)), 255))

this should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Richard Schollar]

Here's another alternative in C column:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">"blah blah blah wskkxp23 blah blah blah"</td><td style=";">wskkxp23</td><td style=";">wskkxp23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">D001</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">"d002dces22 blah blah blah"</td><td style=";">d002dces22</td><td style=";">d002dces22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">D002</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">"blah blah blah"</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T001</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">T002</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">WS</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">A1,LOOKUP(<font color="Red">2^15,SEARCH(<font color="Green">$H$1:$H$5,A1</font>)</font>),FIND(<font color="Red">" ",A1&" ",LOOKUP(<font color="Green">2^15,SEARCH(<font color="Purple">$H$1:$H$5,A1</font>)</font>)</font>)-LOOKUP(<font color="Red">2^15,SEARCH(<font color="Green">$H$1:$H$5,A1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=TRIM(<font color="Blue">LEFT(<font color="Red">SUBSTITUTE(<font color="Green">REPLACE(<font color="Purple">" "&A1,1,LOOKUP(<font color="Teal">2^15,SEARCH(<font color="#FF00FF">$H$1:$H$5,A1</font>)</font>),""</font>)," ",REPT(<font color="Purple">" ",50</font>)</font>),50</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[DarenJohnson]

Simply brilliant!

Thank you guys so so much, both solutions are great. Although I will go with Mike's as it's a little easier to map into my range name etc.

I clearly need to do alot more reading before i come even close to understanding how these work.

Thank you so so much though for bailing me out.
Daren