CODEWITHSUNDEEP
excelexcel-formula
Jean-François Corbett
Jean-François Corbett

Reputation: 38520

Search cell for list of characters

I have a cell listing an arbitrary number of characters, e.g. abc. How do I detect, using an Excel formula, whether a given other cell contains one or more of these characters?

In the example below, the words that have a, b, or c in them return TRUE, while those that don't return FALSE. That's the desired output.

enter image description here

I know how to detect the presence of one character:

=NOT(ISERROR(SEARCH("a",B4)))

and, if I know for sure I have 3 characters, I can combine three of these ISERROR statements like this:

 =NOT(AND(ISERROR(SEARCH("a",B4)),ISERROR(SEARCH("b",B4)),ISERROR(SEARCH("c",B4))))

but how would you do this for an arbitrarily long list of characters, as given in cell A4?

Note that I am not looking for a VBA solution; I can already do that.

Upvotes: 2

Views: 3433

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

Got it!

=NOT(AND(ISERROR(SEARCH(MID($A$4,
    COLUMN($A$1:OFFSET($A$1,0,LEN($A$4)-1))-COLUMN($A$1)+1,1),$B4))))

or minor variation to shorten it a bit:

=NOT(AND(ISERROR(SEARCH(MID($A$4,
    COLUMN(OFFSET($A$1,0,0,1,LEN($A$4)))-COLUMN($A$1)+1,1),$B4))))

entered as an array formula using Ctrl-Shift-Enter, and copied down.

enter image description here

This part 

COLUMN($A$1:OFFSET($A$1,0,LEN($A$4)-1))-COLUMN($A$1)+1

is just a trick to return an array {1, 2, 3, ..., number or chars to search for}. In this example, {1,2,3,4,5}. The last bit

-COLUMN($A$1)+1

is to make sure this still works if someone inserts columns before column A (otherwise the array would no longer start at 1). Example still working when 4 columns added before A:

enter image description here

Instead of that last bit, @EEM suggests hard-coding the $A$1 cell reference in a string and using INDIRECT, such that the cell reference stays fixed regardless of any columns inserted. The resulting formula is a bit longer, but works fine as well:

=NOT(AND(ISERROR(SEARCH(MID($C$4,
     COLUMN(INDIRECT("$a$1"):OFFSET(INDIRECT("$a$1"),0,LEN($C$4)-1)),1),$D4))))

Upvotes: 2

EEM
EEM

Reputation: 6659

Try this formula:

=NOT(AND(ISERROR(SEARCH(MID($A$4,
COLUMN(INDEX($1:$1,1):INDEX($1:$1,LEN($A$4))),1),$B4))))

Basically is an improved version of the formula posted by the OP that eliminates the use the volatile OFFSET function

Upvotes: 2

Dave
Dave

Reputation: 1643

It's not very pretty, but I have a solution if you know the maximum length your input string is going to be:

In Column H numbered list from 1 to [Max Length]

In G1 I entered =MID($A$4,H1,1) and copied down

In I1 I entered =IF(H1<=LEN($A$4),H1,"") and copied down

These can be copied down to as far as the max length of your input field will go

Then the formula in column C 

{=IF(SUM(IFERROR(SEARCH(OFFSET($G$1,0,0,COUNT($I:$I),1),B4),0))>0,"TRUE","FALSE")}

entered as a range formula (ctrl+shift+enter)

The number in column H is used with the Mid function to split the letters out individually. The offset in the main function uses the Count of numbers in column I to determine the size of the Range to look at in column G.

This can be tidied up somewhat but I figured it is easier to see my thinking when it is split out like this.

Upvotes: 0

Related Questions