Custom data validation problem
 

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

Custom data validation problem
 

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.



Neil wrote:

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

--

Dave Peterson

Custom data validation problem
 

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(SUBSTI TUTE(SUBSTITUTE(UPPER(A1),"R",""),"W",""),"B",""), "L",""))=0)

Neil



"Neil" wrote in message
...
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

Custom data validation problem
 

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

--

Regards,

Peo Sjoblom


"Neil" wrote in message
...
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(SUBSTI TUTE(SUBSTITUTE(UPPER(A1),
"R",""),"W",""),"B",""),"L",""))=0)

Neil



"Neil" wrote in message
...
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

Custom data validation problem
 

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

"Peo Sjoblom" wrote in message
...
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

--

Regards,

Peo Sjoblom


"Neil" wrote in message
...
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(SUBSTI TUTE(SUBSTITUTE(UPPER(A1),
"R",""),"W",""),"B",""),"L",""))=0)

Neil



"Neil" wrote in message
...
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