Custom data validation problem

N

Neil

I would like to validate cells according to the following rule ...

The cell can contain the following:
A single zero "0" or
a single period "." or
a string of any length consisting of the letters R, W, B, L

Eg all the following are valid
0
..
rrr
bbbbb
bbrrl
l
wrbblllrrrrbbbbbb

The following are invalid
00
0r
rrrbbbh
..rbl

Is this possible using a custom validation?

Thanks in advance.
Neil
 
D

Dave Peterson

How about:
Data|Validation|Custom:
formula is:
=OR(A1=0,A1="0",A1=".",
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(lower(A1),"r",""),"l",""),
"b",""),"w","")="")
all one line

=substitute() is case sensitive. If you really meant rlbw--and not RLBW, then
get rid of that lower() stuff.
 
N

Neil

It's ok now, I have solved the problem, thanks to MrExcel

Here is how to do it if anyone is interested ...

=OR(A1="0",A1=".",LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"R",""),"W",""),"B",""),"L",""))=0)

Neil
 
P

Peo Sjoblom

You got an answer here from Dave Peterson, do you want people to be able to
enter zeros? Then you should use his solution since the one you got will
only prevent text zeros, not numeric zeros
 
N

Neil

I havn't seen the reply here from Dave Peterson, but I already replaced the
"0" with 0 and it seems to work ok

Thanks for your time
Neil
 
Top