CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Vinnie Van Rooij
Vinnie Van Rooij

Reputation: 121

Find certain characters in cell with Excel

I have a cell where a link can be pasted. For example: https://stackoverflow.com/questions/ask

I want the cell next to it say "wrong" IF the text contains "/" or ":" or "=". It should say "Correct" is none of these 3 characters are found in the text.

For example:
test/
Would give "Wrong" because it contains at least 1 "/"

/test:
Would give "Wrong" because it contains at least 1 ":" and at least 1 "/"

test///////
Would give "Wrong" because it contains at least 1 "/"

test/:=
Would give "Wrong" because it contains at least 1 "/", at least 1 ":" and at least 1 "/"

test123{}+_)
Would give "Correct" because it does not contain any "/", ":" or "="

I tried to work with nested IF, but I couldn't get it to work. I kept getting errors when adding the third character to the formula.

Upvotes: 0

Views: 66

Answers (1)

Mrig
Mrig

Reputation: 11702

Try this

=IF(OR(ISNUMBER(SEARCH("/",A1)),ISNUMBER(SEARCH(":",A1)),ISNUMBER(SEARCH("=",A1))),"Wrong","Correct")

or prefereably this one

=IF(OR(ISNUMBER(SEARCH({"/",":","="},A1))),"Wrong","Correct")

See image for reference.

enter image description here

Upvotes: 3

Related Questions