Cell Validation specific Alpha Numeric format returns false if in wrong format

Does this work?

=AND(LEFT(D31,2)="AS",MID(D3,4,4)+0>0,MID(D3,4,4)+0<=9999,MID(D3,9,4)+0>0,MID(D3,9,4)+0<=9999,MID(D3,14,4)+0>0,MID(D3,14,4)+0<=9999)
Custom Data Validation formula

Note that if they copy and paste into the cell Data Validation will not trigger and will accept what they enter (someone told me recently).

Are they inputting hyphens as well ?

I interpreted the request differently.

Assuming that you only want to allow strings with the following parameters:
1) Alphanumeric with only capital letters
2) Hyphens in character positions 3, 8, and 13
3) 17 character long
4) Starting with "AS"

Try this:

Please Login or Register  to view this content.

Edit: Just realized that this exceeds the formula character limit for DV formulas.
Working on a fix.

I just realized that I misunderstood the request and was making it way more complex than it needed to be.

Assuming these are the parameters:
1) Starts with AS
2) Hyphens at places 3, 8, and 13
3) 17 characters
4) Only numbers after "AS" (not including hyphens)

You can use this DV formula:
=AND(LEFT(D3,2)="AS",MID(D3,3,1)&MID(D3,8,1)&MID(D3,13,1)="---",LEN(D3)=17,ISNUMBER(RIGHT(SUBSTITUTE(D3,"-",""),12)+0))

See attachment.

Originally Posted by 63falcondude

I just realized that I misunderstood the request and was making it way more complex than it needed to be.

Assuming these are the parameters:
1) Starts with AS
2) Hyphens at places 3, 8, and 13
3) 17 characters
4) Only numbers after "AS" (not including hyphens)

You can use this DV formula:
=AND(LEFT(D3,2)="AS",MID(D3,3,1)&MID(D3,8,1)&MID(D3,13,1)="---",LEN(E3)=17,ISNUMBER(RIGHT(SUBSTITUTE(D3,"-",""),12)+0))

See attachment.

Typo you have LEN(E3)=17

I was wondering about leading zeroes in each 4-digit number but you've covered that well!

First thought

AND(LEFT(D3,1)>="A",LEFT(D3,1)<="Z",MID(D3,2,1)>="A",MID(D3,2,1)<="A")

Here's another way:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW($65:$90)),LEFT(D3,2))))=2

Glad we could help. Thanks for the rep!

Edit: The rows should be surrounded by INDIRECT to prevent this from changing if rows are deleted in the workbook.
That would look like this:

=SUMPRODUCT(--ISNUMBER(FIND(CHAR(ROW(INDIRECT("65:90"))),LEFT(D3,2))))=2

Originally Posted by Ratso

I'm assuming the last <="A" should be <="Z"

But it works perfectly.

Thank you both

Yep that's right
My glasses need cleaning