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.

Solution 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.

Solution 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

Comments

[chris neilsen]

-1 Fails unless cell contains all of ? % &

[sigil]

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?

[Rhys Gibson]

Chris' solution is better for that. He shows how to use a range of characters via an array formula.