Restrict data entry to format AA111111A

[EdGilmore]

Firstly, I'm pretty green with Excel, so please go gently with responses and excuse any ignorance hiterto.

I am trying to format a cell so that it only allows an entry in the format AA111111A (2 alpha, 6 numeric, 1 alpha characters) and will return a warning message if anything else is entered. Can this be done either in validation or VBA?

 

[Aladin Akyurek]

Firstly, I'm pretty green with Excel, so please go gently with responses and excuse any ignorance hiterto.

I am trying to format a cell so that it only allows an entry in the format AA111111A (2 alpha, 6 numeric, 1 alpha characters) and will return a warning message if anything else is entered. Can this be done either in validation or VBA?

Let's data-validate A2.

Choose Custom for Allow.

Invoke in the Formula box:

=AND(LEN(A2)=9,1-ISNUMBER(MID(A2,1,1)+0),1-ISNUMBER(MID(A2,2,1)+0),
     ISNUMBER(MID(A2,3,6)+0),1-ISNUMBER(MID(A2,9,1)+0))

 

[T. Valko]

Firstly, I'm pretty green with Excel, so please go gently with responses and excuse any ignorance hiterto.

I am trying to format a cell so that it only allows an entry in the format AA111111A (2 alpha, 6 numeric, 1 alpha characters) and will return a warning message if anything else is entered. Can this be done either in validation or VBA?

Do the alpha characters have be letters A thru Z only? Do they have to be all UPPERCASE?

 

[T. Valko]

Let's data-validate A2.

Choose Custom for Allow.

Invoke in the Formula box:

=AND(LEN(A2)=9,1-ISNUMBER(MID(A2,1,1)+0),1-ISNUMBER(MID(A2,2,1)+0),
     ISNUMBER(MID(A2,3,6)+0),1-ISNUMBER(MID(A2,9,1)+0))

That allows entries like:

AA1111.3A
?~.001E0)
(space)(space)-11E10P

 

[EdGilmore]

The data validation works, but I do need it to be CAPS only, any ideas?

 

[T. Valko]

The data validation works, but I do need it to be CAPS only, any ideas?

Does that mean the "alpha" characters can be any non-numeric character? Like commas, question marks, tilde characters?

That's what Aladin's formula allows.

Is this what you want:

• total length must be 9 characters
• the first 2 characters must be the uppercase letters A to Z
• the next 6 characters must be the digits 0 to 9
• the last character must be an uppercase letter A to Z

 

[Aladin Akyurek]

The data validation works, but I do need it to be CAPS only, any ideas?

That requires an additional test...

The following appears to restrict the first 2 tokens and the 9th token to chars in the uppercase set of A to Z and the 3rd to 8th token set to digits from 0 to 9...

To data-validate A2, set Allow to Custom and invoke in the Formula box:

=AND(
   CODE(MID(LEFT(A2,2)&RIGHT(A2),ROW(INDIRECT("1:3")),1))>=65,
   CODE(MID(LEFT(A2,2)&RIGHT(A2),ROW(INDIRECT("1:3")),1))<=90,
   ISNUMBER(SUM(MID(A2,ROW(INDIRECT("3:8")),1)+0)))

Note that this formula outside a data-validated cell would require
confirming with control+shift+enter.

 

[Scott Huish]

I think that needs 1 additional check because it will allow:

AA123456AA

=AND(CODE(MID(LEFT(A2,2)&RIGHT(A2),ROW(INDIRECT("1:3")),1))>=65,CODE(MID(LEFT(A2,2)&RIGHT(A2),ROW(INDIRECT("1:3")),1))<=90,ISNUMBER(SUM(MID(A2,ROW(INDIRECT("3:8")),1)+0)),LEN(A2)=9)

 

[Aladin Akyurek]

I think that needs 1 additional check because it will allow:

AA123456AA

=AND(CODE(MID(LEFT(A2,2)&RIGHT(A2),ROW(INDIRECT("1:3")),1))>=65,CODE(MID(LEFT(A2,2)&RIGHT(A2),ROW(INDIRECT("1:3")),1))<=90,ISNUMBER(SUM(MID(A2,ROW(INDIRECT("3:8")),1)+0)),LEN(A2)=9)

Right. Thanks.

Aladin

 

[EdGilmore]

Thanks guys, that works perfectly!