Ask Your Question

search array of strings inside other string

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

sampson1952 gravatar image

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

1 "Letter to Bob"

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


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 gravatar imageOpaque ( 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 gravatar imagesampson1952 ( 2020-11-20 12:18:53 +0100 )edit

3 Answers

Sort by » oldest newest most voted

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

Opaque gravatar image

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


you can use the formula by slightly modifying "no match" to 0


Note All values >=1 are boolean TRUE.

Hope that helps.

edit flag offensive delete link more


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

sampson1952 gravatar imagesampson1952 ( 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 gravatar imageOpaque ( 2020-11-20 14:54:10 +0100 )edit

answered 2020-11-20 17:15:23 +0100

igorlius gravatar image

updated 2020-11-20 18:46:04 +0100


I wrote something that might be kind of helpful to you a little while back

edit flag offensive delete link more

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

sampson1952 gravatar image

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 78 times

Last updated: Nov 21 '20