Custom Validation Problem

[Legacy 54891]

Hi

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

(Using Excel2003 on Windows XP)

 

[fairwinds]

Welcome to the board!

Try:

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

 

[Legacy 54891]

Thankyou Fairwinds !

That is superb!!! I understand the function perfectly, but wouldn't have thought of it myself!

I owe you one!