I am trying to determine if any of the individual strings in an array/table of strings (i.e. Sheet2 A1:A4) is a substring in another cell (Sheet1 A1).
Sheet1
A
1 "Letter to Bob"
Sheet2
A
1 Sam
2 Bill
3 Bob
4 Dick
Are any of the strings in Sheet2 A1:A4 inside the Sheet1 A1 string?? (In this example, the answer is Yes… Bob is found in Sheet1 A1)
What would the formula to accomplish this look like??
Not sure what information you really want to have: Check
=IFNA(MATCH(1;NOT(ISNA(REGEX(Sheet1.$A$1;A1:A4)));0);"no match"), which provides either the number (i.e. the index) of the first name in A1:A4 to appear in Sheet1.A1 (here: 3) or “no match”, if no name does appear in Sheet1.A1
Thank you. I just need a boolean if one was found. Thank you… I will examine your example to better understand your solution.
Hello,
you can use the formula by slightly modifying “no match” to 0
=IFNA(MATCH(1;NOT(ISNA(REGEX(Sheet1.$A$1;A1:A4)));0);0)
Note All values >=1 are boolean TRUE.
Hope that helps.
Excellent… thank you, Opaque, for you time and effort in helping me with this.
If the answers works for you, please consider to click the check mark (
) next to the answer. Thanks in advance …
Hello,
I wrote something that might be kind of helpful to you a little while back
From Opaque’s suggestion, I realized it would be easier to use Regular Expressions with just strings, rather than trying to make it work with an array of cells. So this is my live solution. Thank you Opaque.
=IF( ISBLANK( K$4 ), 0 ,IF( ISNA(REGEX( CONCAT($C69:$D69) , K$4 ) ) , 0, $E69 *-1 ) )
I am using it to examine a bank statement. When I find a specific string in the transaction description, then the transaction amount get’s displayed. This way it generates an expense report.
Columns C:D contain the transaction description Cell K$4 contains the regular expression Column E contains the transaction amount If Cell K$4 is blank, that means it has not yet been defined, so it is ignored