Searching a string from an array of characters

**PaddyDarby** · 03-31-2010, 08:03 PM

Hi folks,
I'm working with a spreadsheet where in A1 I have a variable length text string.
I also have an array of single text characters in eg. A2:Y2 (variable length array some cells may be "").
eg.
A1 = "hrsgo isduf gi"
A2 = "a"
B2 = "c"
function will return "TRUE", but if "g" is in C2 it'll return FALSE....

I need to find out if the string in A1 contains any of the characters in A2:Y2.

I could do this by doing 25 nested IFs with a formula like...
"=IF(NOT(ISERROR(FIND(A2,A1))),TRUE,FALSE)"
... but there's gotta be an easier way. Is it possible to use an array as the Find_Text input to the FIND() function? Or is there another way to do this?

Please keep it simple or give me an idiots guide to the answer as I'm self taught on this sort of stuff. I hope the explanation above makes sense to you....

Thanks for any help Paddy.

**JBeaucaire** · 03-31-2010, 08:31 PM

As long as none of the cells in A2:Y2 are empty, this array formula should work:

=IF(SUM(--(ISNUMBER(SEARCH($A$2:$Y$2, $A$1)))),TRUE,FALSE)

...confirmed with CTRL-SHIFT-ENTER.

**DonkeyOte** · 04-01-2010, 02:54 AM

Assuming Nulls should not be matched (?) then

=SUMPRODUCT((A2:Y2<>"")*ISNUMBER(FIND(A2:Y2,A1)))=0

above uses FIND assuming case sensitivity is required (per OP) - if not switch FIND to SEARCH.

**PaddyDarby** · 04-01-2010, 11:58 AM

Thanks for the prompt replies folks.

JBeaucaire - didn't manage to get yours to work even with a full array ?!?!?

DonkeyOte - thanks did exactly what I needed !!! (Is there any chance you could explain how it works as I can't figure it out ?)

I have another calculation that uses similar inputs but needs to return true if ALL the character values in the array are in the string, any idea how this can be done?

Thanks Paddy

**DonkeyOte** · 04-01-2010, 02:09 PM

Originally Posted by PaddyDarby

Is there any chance you could explain how it works as I can't figure it out ?

The formula conducts a FIND test for each cell in the range A2:Y2 against the contents of A1.
If the term being "iterated" (A2:Y2) is both non-blank/non-null and can be found embedded within contents of A1 the resulting value is 1 else 0 *
The various results (1/0) are summed.
It follows that if the total of the summation is 0 then you know none of the terms in A2:Y2 can be found within the contents of A1.

For an excellent overview of the SUMPRODUCT function see the link in my signature to Bob Phillips' white paper.

Originally Posted by PaddyDarby

I have another calculation that uses similar inputs but needs to return true if ALL the character values in the array are in the string, any idea how this can be done?

On which basis perhaps the below would suffice ?

=SUMPRODUCT((A2:Y2<>"")*ISERROR(FIND(A2:Y2,A1)))=0

In this case we're doing the opposite - ie we want to count as 1 wherever a given term (A2:Y2) can not be found within the contents of A1... once all results are summed we know that should the result be 0 then all terms listed must exist within A1.

* - footnote re: Boolean coercion:

TRUE*TRUE -> 1
TRUE*FALSE -> 0
FALSE*FALSE -> 0

this is because in native XL TRUE coerced to integer equivalent is 1 and FALSE is 0