Prohibit certain characters in cells

M

MarreMan

Hi,
I would like to prohibit the users of inserting the characters "
\/:%'*?<>|". " into some cells. I tried the validation function but couldn´t
find it. I guess there should be some easy way of doing this but I haven´t
found it. Any help appreciated.

Kind regards,
Martin
 
P

Peo Sjoblom

Use validation and custom then formula

=AND(D1<>"*",D1<>"%",D1<>"<>",D1<>"?")

add whatever characters you need

of course it won't help if the users copy and paste characters

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
M

MarreMan

Hi,
thanks for the quick answer. I didn´t get it to work though. I inserted the
line

=AND(B:B<>"*",B:B<>"%",B:B<>"<",B:B<>">",B:B<>"?",B:B<>"\",B:B<>"/",B:B<>"'",B:B<>":",B:B<>"|")

into the Custom field in Validation but I got formula error. Any thoughts?

Kind regards,
Martin Tibblin
 
P

Peo Sjoblom

You need to use a single cell address, then just copy the validation as long
as needed, so select
(don't use all 65536 rows because I don't really believe you need all that)
the range from B1 down,
then with B1 as active cell use

=AND(B1<>" and so on,

that will copy down the validation

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
M

Marre

Hi,
I have tried the below code and it works! Thank you very much!! The only
flaw I could find was that Excel doesn´t accept " as a character to FIND but
thats a minor issue.

Thank you very much for your help Berislav!

Kind regards,
Martin Tibblin
 
B

b&s

Hi,
If I understand well, try this:
.... FIND("""";B1) ...

Is this what you had in mind?
 
Top