Data Validation Formula needed

[rawr19911]

I have been playing around with Data validation trying to get something that will allow a user only to enter the format below when typing in their number

IT has to have PR to start and 5-7 numbers attached
Example PR12345 or PR1234567 will work
however;
PR12345678 will not work
DD123456 Will not work
PR1234 will not work
because they did not either A) lead with PR or B) have to many numbers

 

[n1lesh]

try this:
=AND(LEFT(A1,2)="PR",LEN(A1)>6,LEN(A1)<10)

 

[Scott T]

Try
assuming the PR needs to be capitalized.

=AND(EXACT(LEFT(A1,2),"PR"),ISNUMBER(TRIM(MID(A1,3,8))+0),LEN(TRIM(MID(A1,3,8))+0)>=5,LEN(TRIM(MID(A1,3,8))+0)<=7)

 

[rawr19911]

try this:
=AND(LEFT(A1,2)="PR",LEN(A1)>6,LEN(A1)<10)

This one worked- Thanks

 

[n1lesh]

You should use this from Scott Tas it validates all errors from user

=AND(EXACT(LEFT(A1,2),"PR"),ISNUMBER(TRIM(MID(A1,3,8))+0),LEN(TRIM(MID(A1,3,8))+0)>=5,LEN(TRIM(MID(A1,3,8))+0)<=7)