Data Validaiton formula containing numbers and text

[madams_08]

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

 

[T. Valko]

I do manually enter the data.. and I still get a error message

Yes, you should get the error message since those entries aren't valid.

00000000XX = 8 digits followed by 2 letters

This data validation formula:

=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)

Will only allow entries that start with 8 digits (from 0 - 9) followed by 2 letters (A - Z, any case).

12345678y = 8 digits followed by 1 letter = invalid entry
87654321d = 8 digits followed by 1 letter = invalid entry

 

[T. Valko]

Nevermind I got the formula to work in a blank spreadsheet. I need to figure out what is wrong with my cell. Thank you sooo much for all of your help!!!

Good deal. Thanks for the feedback!

 

[madams_08]

what would the equation be for 8 digits and 1 letter

 

[T. Valko]

what would the equation be for 8 digits and 1 letter

Try this...

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

If you test the formula on the worksheet it must be array entered.