search array of strings inside other string

asked 2020-11-20 11:33:17 +0100

sampson1952
21 ●1 ●1 ●6

updated 2020-11-20 11:58:10 +0100

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??

edit retag flag offensive close merge delete

Comments

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

Opaque ( 2020-11-20 11:57:50 +0100 )edit

Thank you. I just need a boolean if one was found. Thank you... I will examine your example to better understand your solution.

sampson1952 ( 2020-11-20 12:18:53 +0100 )edit

add a comment

1

answered 2020-11-20 12:27:21 +0100

Opaque
12030 ●3 ●23 ●94

updated 2020-11-20 12:32:17 +0100

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.

edit flag offensive delete link

Comments

Excellent... thank you, Opaque, for you time and effort in helping me with this.

sampson1952 ( 2020-11-20 14:48:26 +0100 )edit

If the answers works for you, please consider to click the check mark (✔) next to the answer. Thanks in advance ...

Opaque ( 2020-11-20 14:54:10 +0100 )edit

add a comment

1

answered 2020-11-21 18:11:29 +0100

sampson1952
21 ●1 ●1 ●6

updated 2020-11-21 18:13:56 +0100

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