ExcelBanter - Data Validation of user input

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Data Validation of user input (https://www.excelbanter.com/excel-worksheet-functions/185505-data-validation-user-input.html)

Data Validation of user input

I need a function to text user input. The input must be 10 characters in
length, consist of 5 digits, 1 letter and then 4 digits (a property barcode).
Examples:
00015D6345
01451A0011
Any other format will get an error message.
Can someone help me?
--
Dan

Data Validation of user input

Use DV with a formula of

=AND(LEN(A1)=10,ISNUMBER(--(LEFT(A1,5))),CODE(LOWER(MID(A1,6,1)))=97,CODE(LO WER(MID(A1,6,1)))<=122,ISNUMBER(--(RIGHT(A1,4))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dan" wrote in message
...
I need a function to text user input. The input must be 10 characters in
length, consist of 5 digits, 1 letter and then 4 digits (a property
barcode).
Examples:
00015D6345
01451A0011
Any other format will get an error message.
Can someone help me?
--
Dan