Data Validation - Only Allowing certain characters
 

Is it possible to use data validation to restrict what characters can be
entered by a user?

E.g.

Restricted characters: A B C D E

So if a user enters "BAD", it will be accepted, however if he/she enters
"CAT" an error msg is shown because the character T is not allowed.

I've tried creating a formula using the Find method, but it doesn't seem to
know what to do. Is there a "Contain" text function in Excel?

Also, I've tried using the Formatting of cells, but it still allows for
entry. My last option will be to use VBA, but I'm trying not to.

Any assistance will be much appreciated. Thanks.

Data Validation - Only Allowing certain characters
 

You can use a formula of

=SUMPRODUCT(--(ISNUMBER(MATCH(MID(E1,ROW(INDIRECT("1:"&LEN(E1))) ,1),$M$1:$M$5,0))))=LEN(E1)

assuming E1 is the DV cell, and M1:M5 hold the allowable characters.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Marcus" wrote in message
...
Is it possible to use data validation to restrict what characters can be
entered by a user?

E.g.

Restricted characters: A B C D E

So if a user enters "BAD", it will be accepted, however if he/she enters
"CAT" an error msg is shown because the character T is not allowed.

I've tried creating a formula using the Find method, but it doesn't seem
to
know what to do. Is there a "Contain" text function in Excel?

Also, I've tried using the Formatting of cells, but it still allows for
entry. My last option will be to use VBA, but I'm trying not to.

Any assistance will be much appreciated. Thanks.