Custom data validation problem

  • Thread starter Thread starter Neil
  • Start date Start date
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
 
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.
 
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
 
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
 
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
 
Back
Top