Search within a string for 1 of 3 words

[chaz_mac]

I wish to search within a string for one of three words, and depending on which one i find further parse the string contents.

An equation like

IF(SEARCH("WORD 1",$A$1,1)<>VALUE, MID(), IF(SEARCH("WORD 2") ...

always does not work because the #VALUE! error code is returned for the words not found, making the whole equation return the error code.

Must I write a custom function to avoid this?

Thanks to all the people who help on the board -

C

 

[Yogi Anand]

Hi chaz mac:

I suggest you post a few rows of your sample data along with your expected result ... to help the viewers see what you are working with.

 

[chaz_mac]

I don't think i need to post rows, I'm looking at a single cell. Its contents are of the form:

CASE 12,500 FT HOLD ISA M=0.40 TIME=15:55 21 MAY 2008

I search for the word HOLD, knowing that the following string tells me something, and that the phrase M= delimits the end of the target string.

So my equation is:

="Amb"&TRIM(MID($A$2,SEARCH("HOLD",$A$2,1)+5,SEARCH("M=",$A$2,1)-SEARCH("HOLD",$A$2,1)-5))

and I get the result

AmbISA

But, in some cases, the word HOLD is CRUISE or ICING. The general format is the same, but I cannot do IF(SEARCH(HOLD) etc because the search function returns an error code that propagates through the equation for the words not found anywhere in the equation.

I hope that is clear ..

C

 

[Aladin Akyurek]

I don't think i need to post rows, I'm looking at a single cell. Its contents are of the form:

CASE 12,500 FT HOLD ISA M=0.40 TIME=15:55 21 MAY 2008

I search for the word HOLD, ...

...And what is the result you want if that word is found?

 

[pgc01]

Hi

Try:

="Amb"&TRIM(MID($A$2,MIN(SEARCH({"HOLD","CRUISE","ICING"},$A$2&"HOLDCRUISEICING",1)+LEN({"HOLD","CRUISE","ICING"})),SEARCH("M=",$A$2,1)-MIN(SEARCH({"HOLD","CRUISE","ICING"},$A$2&"HOLDCRUISEICING",1)+LEN({"HOLD","CRUISE","ICING"}))))

Or use an auxilliary cell, it will make it easier to read and mantain:

In B2:

=MIN(SEARCH({"HOLD","CRUISE","ICING"},$A$2&"HOLDCRUISEICING",1)+LEN({"HOLD","CRUISE","ICING"}))

The result:

="Amb"&TRIM(MID($A$2,B2,SEARCH("M=",$A$2,1)-B2))

 

[chaz_mac]

pgc01 -

never thought of using search in an array formula, neat.

Thanx

C