I read a previous article and non of the formulas worked for my worksheet.

I need to validate a cell for the following custom format, but cant get it to work:

1. XX00000

2. 00000000XX

3. 000000X

Where XX is any combination of letters (upper or lowercase) and

00000 is a combination of any 5 numbers.

All of these formaulas can not allow special characters.

It must always be in this format, with this number of digits in total on each

Try these...

For XX00000:

=AND(LEN(A2)=7,ABS(CODE(UPPER(A2))-77.5)<13,ABS(CODE(UPPER(MID(A2,2,1)))-77.5)<13,COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5)

For 00000000XX:

=AND(LEN(A2)=10,ABS(CODE(UPPER(MID(A2,9,1)))-77.5)<13,ABS(CODE(UPPER(MID(A2,10,1)))-77.5)<13,COUNT(-MID(A2,ROW(INDIRECT("1:8")),1))=8)

For 000000X:

=AND(LEN(A2)=7,ABS(CODE(UPPER(RIGHT(A2)))-77.5)<13,COUNT(-MID(A2,ROW(INDIRECT("1:6")),1))=6)

If you test those formulas on the worksheet they must be array entered.

Array formulas need to be entered using the key

combination of CTRL,SHIFT,ENTER (not just ENTER).

Hold down both the CTRL key and the SHIFT key

then hit ENTER.