Custom Validation Problem

L

Legacy 54891

Guest
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)
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome to the board!

Try:

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

Legacy 54891

Guest
Thankyou Fairwinds !

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

I owe you one!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,029
Messages
5,569,747
Members
412,289
Latest member
Kingchaos64
Top