searching a string of text for multiple words

**easty** · 12-09-2009, 04:08 AM

Hi there,

its probably best I give you an example.

A1 contains the following text

Australia & New Zealand Banking Group Ltd Ordinary dividend on 3750 shares at 62.00 cents

B1 contains $2,325

in C1 I want a formula that says.....if A1 contains both Australia and 62.00 I want the result to be B1

Your help is much appreciated.

cheers

**DonkeyOte** · 12-09-2009, 04:16 AM

If we assume Australia always precedes the value you can use one SEARCH

=IF(ISNUMBER(SEARCH("Australia*62.00",A1)),B1,"")

(the above would return B1 if say you had 262.00 in A1... if the number is always preceded by a space add a space to your string before the 62 (post *) so as to make exclusive)