Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.