Use Validation to force specific entry of first 3 characters in st

R

ron

What formula should I enter into Category "Custom" of Validatioan to force
user to begin character string with "PR-"? Desired result is "PR-001". I
don't want user to have option to enter "001" or "002", etc., without the
"PR-". Nor do I want them to be able to enter "DR-001".
 
A

Arvi Laanemets

Hi

An example for column A, the formatting starts from A1
=(LEFT($A1,3)="PR-")

Whe you also need all entries to be unique, then
=AND(LEFT($A1,3)="PR-",COUNTIF($A:$A,$A1)<2)


Arvi Laaneemts
 
D

Dave Peterson

=LEFT(A1,3)="Pr-"
or maybe...
=EXACT("PR-",LEFT(A1,3))

(with A1 being validated.)
 
R

ron

You people are really really good. Thanks. I modified the formula to ensure a
6 digit entry:
=AND(LEFT(E26,3)="Pr-",LEN(E26)=6)
 
R

ron

You people are really really good. Thanks. I modified the formula to ensure a
6 digit entry:
=AND(LEFT(E26,3)="Pr-",LEN(E26)=6)
 
Top