software

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

The cell can contain the following:
A single zero quot;0quot; or
a single period quot;.quot; 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=quot;0quot;,A1=quot;.quot;,
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(lower( A1),quot;rquot;,quot;quot;),quot;lquot;,quot;quot;),
quot;bquot;,quot;quot;),quot;wquot;,quot;quot;)=quot;quot;)
all one line

=substitute() is case sensitive. If you really meant rlbw--and not RLBW, then
get rid of that lower() stuff.
Neil wrote:
gt;
gt; I would like to validate cells according to the following rule ...
gt;
gt; The cell can contain the following:
gt; A single zero quot;0quot; or
gt; a single period quot;.quot; or
gt; a string of any length consisting of the letters R, W, B, L
gt;
gt; Eg all the following are valid
gt; 0
gt; .
gt; rrr
gt; bbbbb
gt; bbrrl
gt; l
gt; wrbblllrrrrbbbbbb
gt;
gt; The following are invalid
gt; 00
gt; 0r
gt; rrrbbbh
gt; .rbl
gt;
gt; Is this possible using a custom validation?
gt;
gt; Thanks in advance.
gt; Neil

--

Dave Peterson

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

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

=OR(A1=quot;0quot;,A1=quot;.quot;,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(UPPER(A1),quot;Rquot;,quot;quot;),quot;Wquot;,quot;quot;),quot;Bquot;,quot;quot;), quot;Lquot;,quot;quot;))=0)

Neil
quot;Neilquot; gt; wrote in message
...
gt;I would like to validate cells according to the following rule ...
gt;
gt; The cell can contain the following:
gt; A single zero quot;0quot; or
gt; a single period quot;.quot; or
gt; a string of any length consisting of the letters R, W, B, L
gt;
gt; Eg all the following are valid
gt; 0
gt; .
gt; rrr
gt; bbbbb
gt; bbrrl
gt; l
gt; wrbblllrrrrbbbbbb
gt;
gt; The following are invalid
gt; 00
gt; 0r
gt; rrrbbbh
gt; .rbl
gt;
gt; Is this possible using a custom validation?
gt;
gt; Thanks in advance.
gt; Neil
gt;
gt;
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 Sjoblomquot;Neilquot; gt; wrote in message
...
gt; It's ok now, I have solved the problem, thanks to MrExcel
gt;
gt; Here is how to do it if anyone is interested ...
gt;
gt;
=OR(A1=quot;0quot;,A1=quot;.quot;,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(UPPER(A1),
quot;Rquot;,quot;quot;),quot;Wquot;,quot;quot;),quot;Bquot;,quot;quot;),quot;Lquot;,quot;quot;))=0)
gt;
gt; Neil
gt;
gt;
gt;
gt; quot;Neilquot; gt; wrote in message
gt; ...
gt; gt;I would like to validate cells according to the following rule ...
gt; gt;
gt; gt; The cell can contain the following:
gt; gt; A single zero quot;0quot; or
gt; gt; a single period quot;.quot; or
gt; gt; a string of any length consisting of the letters R, W, B, L
gt; gt;
gt; gt; Eg all the following are valid
gt; gt; 0
gt; gt; .
gt; gt; rrr
gt; gt; bbbbb
gt; gt; bbrrl
gt; gt; l
gt; gt; wrbblllrrrrbbbbbb
gt; gt;
gt; gt; The following are invalid
gt; gt; 00
gt; gt; 0r
gt; gt; rrrbbbh
gt; gt; .rbl
gt; gt;
gt; gt; Is this possible using a custom validation?
gt; gt;
gt; gt; Thanks in advance.
gt; gt; Neil
gt; gt;
gt; gt;
gt;
gt;
I havn't seen the reply here from Dave Peterson, but I already replaced the
quot;0quot; with 0 and it seems to work ok

Thanks for your time
Neil

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; You got an answer here from Dave Peterson, do you want people to be able
gt; to
gt; enter zeros? Then you should use his solution since the one you got will
gt; only prevent text zeros, not numeric zeros
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt;
gt; quot;Neilquot; gt; wrote in message
gt; ...
gt;gt; It's ok now, I have solved the problem, thanks to MrExcel
gt;gt;
gt;gt; Here is how to do it if anyone is interested ...
gt;gt;
gt;gt;
gt; =OR(A1=quot;0quot;,A1=quot;.quot;,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(UPPER(A1),
gt; quot;Rquot;,quot;quot;),quot;Wquot;,quot;quot;),quot;Bquot;,quot;quot;),quot;Lquot;,quot;quot;))=0)
gt;gt;
gt;gt; Neil
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Neilquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I would like to validate cells according to the following rule ...
gt;gt; gt;
gt;gt; gt; The cell can contain the following:
gt;gt; gt; A single zero quot;0quot; or
gt;gt; gt; a single period quot;.quot; or
gt;gt; gt; a string of any length consisting of the letters R, W, B, L
gt;gt; gt;
gt;gt; gt; Eg all the following are valid
gt;gt; gt; 0
gt;gt; gt; .
gt;gt; gt; rrr
gt;gt; gt; bbbbb
gt;gt; gt; bbrrl
gt;gt; gt; l
gt;gt; gt; wrbblllrrrrbbbbbb
gt;gt; gt;
gt;gt; gt; The following are invalid
gt;gt; gt; 00
gt;gt; gt; 0r
gt;gt; gt; rrrbbbh
gt;gt; gt; .rbl
gt;gt; gt;
gt;gt; gt; Is this possible using a custom validation?
gt;gt; gt;
gt;gt; gt; Thanks in advance.
gt;gt; gt; Neil
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;