# Search within a string for 1 of 3 words

#### chaz_mac

##### Board Regular
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Yogi Anand

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
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

##### Board Regular
pgc01 -

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

Thanx

C

Replies
1
Views
202
Replies
6
Views
428
Replies
3
Views
660
Replies
1
Views
872
Replies
0
Views
101

1,191,614
Messages
5,987,685
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back