Data Validation Specific Text and Number

[Tashat]

Hi There

I want to set some data validation on a column to only allow the user to input data of a specific format of text and numbers. Each entry must start with "CON" and then be followed by 6 digits. E.g. CON000001, CON145098

I have managed to do a customer data validation to force the user to enter "CON" as the first part of the validation, but not sure how to edit the formula to include the 6 digits (numbers) as part of the validation criteria.

=ISNUMBER(FIND("CON",H6))

Any help would be much appreciated.

 

[Marcelo Branco]

Maybe...

=AND(LEN(H6)=9,ISNUMBER(FIND("CON",H6)),ISNUMBER(--RIGHT(H6,6)),ISERROR(SEARCH("e",H6)))

M.

 

[Tashat]

Maybe...

=AND(LEN(H6)=9,ISNUMBER(FIND("CON",H6)),ISNUMBER(--RIGHT(H6,6)),ISERROR(SEARCH("e",H6)))

M.

That's perfect! Thank you.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.