CODEWITHSUNDEEP
excelif-statementsearch
Jack Armstrong
Jack Armstrong

Reputation: 1249

Search inside a cell

I am trying to return a Boolean value for if a cell has either a # or a @ in it.

I have the formula =if(isnumber(search("@",CELL))=TRUE,"Yes","No")

The CELL is what cell I am searching in. These possible cells could have numbers and letters in any combination. I just need to see if they have an @ or a # in them.

Upvotes: 0

Views: 43

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

Alternate formula:

=IF(SUMPRODUCT(COUNTIF(A1,"*"&{"@","#"}&"*"))>0,"YES","NO")

On Excel 2013 (maybe 2010?) and higher you can replace Sumproduct with just Sum without having to array enter. And then you can just add additional items you're looking for in that list instead of having separate ISNUMBER(SEARCH statements for each one. You could even put those items in a cell range and just reference that cell range like:

=IF(SUMPRODUCT(COUNTIF(A1,"*"&$D$1:$D$2&"*"))>0,"YES","NO")

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152465

Use OR():

=IF(OR(ISNUMBER(SEARCH("@",A1)),ISNUMBER(SEARCH("#",A1))),"YES","NO")

enter image description here

Upvotes: 1

Related Questions