Data Validation

[Hazeyt]

Please can someone help with Data Validation.

I want it to be 8 digits only.

1st digit must be a T

The rest are numbers but the last digit may be a number or letter.

Many thanks

 

[awoohaw]

try this:

 

[awoohaw]

try this:
View attachment 86421

Entire formula is: =AND(LEFT(C1,1)="T",ISNUMBER(1*MID(C1,2,6)),LEN(C1)=8)

 

[JvdV]

A, for me personally, fun option:

=NOT(ISERROR(FILTERXML("<t><s>"&A1&"</s></t>","//s[starts-with(.,'T')][string-length()=8][substring(.,2,6)*0=0][translate(substring(., 8), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','')='']")))

It's behaving as some pattern check in an anding way:

• [starts-with(.,'T')] - Check that the input starts with an uppercase T (xpath is case sensititve);
• [string-length()=8] - Check that the input is 8 characters long;
• [substring(.,2,6)*0=0] - Check that the character from index 2 upto 7 are digits;
• [translate(substring(., 8), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','')=''] - Check that the 8th character is equal to any uppercase letter or a digit.

 

[RoryA]

I guess fun, like beauty, is in the eye of the beholder.

 

[Hazeyt]

Entire formula is: =AND(LEFT(C1,1)="T",ISNUMBER(1*MID(C1,2,6)),LEN(C1)=8)

Thank you

 

[awoohaw]

happy to help. best wishes!