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)),"Contain s","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

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


"Christopher Naveen" wrote:

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)),"Contain s","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

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

"Christopher Naveen" wrote in
message ...
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)),"Contain s","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

Try this:

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

Copy down as needed

--
Biff
Microsoft Excel MVP


"Christopher Naveen" wrote in
message ...
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)),"Contain s","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

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

A few keystrokes shorter:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

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

Copy down as needed

--
Biff
Microsoft Excel MVP


"Christopher Naveen" wrote
in message ...
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)),"Contain s","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