How to validate for a set of characters in Excel?

Question

I'd like to make a validation rule that prohibits the entry of certain characters in a given cell. For example, if my search set of characters are ("/","&","%"), then I should get the following search results:

"Test, test" = false
"Te/st" = true
"Test...test&"= true

and the second two examples should not be allowed.

I guess I'm looking for something similar to SQL's WHERE...IN grammar. How would I do this in Excel? I know I can just use OR() or nested IF() statements, but I'm wondering if there's something cleaner.

Answer 1

if you're using Excel 2007, you could try something based on:

=IFERROR(FIND("%",A1),0)+IFERROR(FIND("&",A1),0)+IFERROR(FIND("/",A1),0)

which returns 0 if the string is valid and a positive number if it isn't.

if you're using 2003, then you'll need to change the IFERRORs into IF(ISERROR)s. e.g.

=IF(ISERROR(FIND("%",A1)),0,1)+IF(ISERROR(FIND("&",A1)),0,1)+IF(ISERROR(FIND("/",A1)),0,1)

which gives you 0 if the string is valid and 1 if it isn't.

Note: Your logic is negative (i.e. you're using TRUE for and error and FALSE for no error), but it's easy enough to reverse if you want to.

Answer 2

Heres another option

=SUM(COUNTIF(A1,{"*%*","*&*","*/*"}))>0

To get the char list from a range:

If string to test is in A2, and list of char's is in A1:C1

=SUM(COUNTIF(A2,"*"&($A$1:$C$1)&"*"))>0

Answer 3

You can use multiple find-calls. Test if it is a number, then it contains a forbidden character.

isnumber(find("/", cell)+find("%", cell)+find("&", cell))

WRONG - checks if all characters are in the cell. I've not testet the whole statement because my excel's got another localization and I've been to lazy. Should've been semi-colons, too. @Rhys Gibsons answer is correct, thanks @chris neilsen for hinting!