How to validate for a set of characters in Excel?

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.

-

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.

-
Return `>0` if the string is invalid, actually the number of invalid characters. Surreond with `(...)>0` to return TRUE/FALSE (or `(...)=0` to invert the logic). Can also be entered a bit more compactly as `=SUM(IF(ISERROR(FIND({"%","&","/"},A3)),0,1))>0` – chris neilsen Oct 20 '11 at 0:23
This answer solves the problem as I stated it, but since I posted my question I've been thinking I'd like to find a solution that relies on a range to get the list of characters to search for. That way I don't have to change the formula in every cell if I ever decide to change which characters I'm looking for. Any ideas? – sigil Oct 20 '11 at 17:17
Chris' solution is better for that. He shows how to use a range of characters via an array formula. – Rhys Gibson Oct 20 '11 at 22:52

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
``````
-

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!

-
-1 Fails unless cell contains all of `?` `%` `&` – chris neilsen Oct 19 '11 at 22:43