Data VAlidation - Text Length & Character Type

Question

I would like to add some data validation on a cell in the Excel 10. I would like to force the user to enter a string that is 9-char long, with the first 8 char as digits, and the last char an Upper letter.

Thanking u in advance

Answer 1

this formula could help:

=and(isnumber(left(A1,8)+0),len(A1)=9,code(right(A1))>=65,code(right(A1))<=90)

• isnumber(left(A1,8)+0) = with the first 8 char as digits
• len(A1)=9 = 9-char long
• code(right(A1))>=65,code(right(A1))<=90 = char between "A" and "Z"