Text string searches

[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..

 

[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.

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

 

[Dirk]

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

 

[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.

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


Regards
Claus Busch