Search Formual Error

[Christopher Naveen]

Hi,

I am trying search a particular character in a cell and i used "Search"
Function. Here is the formual I used but it is returning error.

=IF(OR(SEARCH("ABC",A1),SEARCH("123",A1)),"Contains","Does not contain")

the requirement is to check 2 different characters in the cell. So I used OR
function.

Result
A B
ABC 123 Contains
ABC #VALUE!
123 #VALUE!
EL #VALUE!


The second and third row should return the value as "contains" but it is
returning error. Can anyone help me to fix this.

Thanks in advance!

Christ

 

[Teethless mama]

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"ABC","123"},A1))),"Contains","Does not
contain")

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter)

=IF(OR(MID(A1,ROW($1:$100),3)="ABC",MID(A1,ROW($1:$100),3)="123"),"Contains","Not")

Please let me know how this works.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Try this:

=IF(OR(ISNUMBER(SEARCH({"abc",123},A1))),"contains","does not contain")

Copy down as needed

 

[T. Valko]

=IF(OR(ISNUMBER(SEARCH({"abc",123},A1))),"contains","does not contain")

A few keystrokes shorter:

=IF(COUNT(SEARCH({"abc",123},A1)),"contains","does not contain")