Text string searches

D

Dirk

Hi,

I have a situation where I have a cell that contains a text string and I want to determine if the string contains any of the subtrings that I have in a pre-defined range of cells. For example if I had the string "This is a EXCEL test of a function" and wanted to see if the string contained any of the following substrings a) "a EXCEL test" b) "this is a test" c) "reserved word". The list of substrings are defined in a named range.

I would like to solve this without using VBA. Any help would be appreciated..
 
C

Claus Busch

Hi Dirk,

Am Wed, 11 Jul 2012 07:38:34 -0700 (PDT) schrieb Dirk:
I have a situation where I have a cell that contains a text string and I want to determine if the string contains any of the subtrings that I have in a pre-defined range of cells. For example if I had the string "This is a EXCEL test of a function" and wanted to see if the string contained any of the following substrings a) "a EXCEL test" b) "this is a test" c) "reserved word". The list of substrings are defined in a named range.
Click to expand...

your string in A1, the range name of substrings is "Substrings":
=COUNT(SEARCH(Substrings,A1))>0
and enter with CRTL+Shift+Enter

Regards
Claus Busch
 
D

Dirk

Thanks for the help. It is greatly appreciated. Is it possible to apply the formula to multiple cells.
 
C

Claus Busch

Hi Dirk,

Am Wed, 11 Jul 2012 09:14:14 -0700 (PDT) schrieb Dirk:
Thanks for the help. It is greatly appreciated. Is it possible to apply the formula to multiple cells.
Click to expand...

what do you mean with apply to multiple cells?
Can you post an example?


Regards
Claus Busch
 
Top