Data Validation for mixed text and numeric with formula only?

Question

Is it possible to validate a string with  text and numbers  with a formula using data validation? 

in Column A, Starting in cell A1   

I want only 8 character string of 2 capital letters only, six numbers only, without space and symbol. like this:

AA123123

not like this :

aa123123

Aa123123

aA123123

aa-123123

AA_123123

is it possible to use a Custom Data Validation formula to ensure the user follows this format?

Answer 1

Hi,

If so, please take this formula:

=AND( <br><br>  AND(OR(CODE(LEFT(F14,1))=68,CODE(LEFT(F14,1))=83),OR(CODE(MID(F14,2,1))=68,CODE(MID(F14,2,1))=83)), <br><br>  ISERROR(MID(F14,2,1)+0), <br><br>  AND(ISNUMBER(RIGHT(F14,6)+0),LEN(RIGHT(F14,6))=6), <br><br>  LEN(F14)=8)

It allows only D or S in capital letters in addition to other restrictions:

DS312469 (Allowed)

SD312469 (Allowed)

DSS312469 (Not Allowed)

sD312469 (Not Allowed)

ds312469 (Not Allowed)

Hi,

When I copy that formula it's error.

Sorry about that!

Please change all F14 in the formula to A1.

Answer 2

Hi,

Please try this formula:

=AND(

AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),ISERROR(LEFT(A1,1)+0)),

ISERROR(MID(A1,2,1)+0),

AND(ISNUMBER(RIGHT(A1,6)+0),LEN(RIGHT(A1,6))=6),

LEN(A1)=8)

Highlight column A and then copy the above formula as it is in the data validation formula box.

Answer 3

Hi,

Thank you very mush for your reply.

I want to prevent them at all please.

Answer 4

Hi,

What about the other letters?

Do you want to restrict them by small letters? Or to prevent them at all?

Answer 5

Thank you so mush

I have more question. If I want specific capital letter is DSonly can not type another capital letter , six numbers only, without space and symbol. like this: 

DS312469

Is it possible to use a Custom Data Validation formula to ensure the user follows this format?