Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
    
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
share|improve this answer

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!

share|improve this answer
    
-1 Fails unless cell contains all of ? % & – chris neilsen Oct 19 '11 at 22:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .