Cell to have only 2 letters and 4 numbers

[Malcolm torishi]

Is it possible to ensure a user can only type into a cell two letters say “ks” followed by four numbers . So if a user forgets either the letters or four numbers he will be prompted.

 

[cooper645]

if your data is in cell A1,

then use data validation > Custom with the following rule;

=AND(LEN(A1)=6,ISTEXT(LEFT(A1,2)),ISNUMBER(VALUE(RIGHT(A1,4))))

then copy down through column A, or adjust as necessary.

This works on Mac, you may have to change ISNUMBER for ISNUMERIC for windows, I'm not 100% sure though.

=AND multiple checks to be true

(LEN(A1)=6, Length of A1 should be 6 characters long

ISTEXT(LEFT(A1,2)), left 2 characters of A1 should be text

ISNUMBER(VALUE(RIGHT(A1,4)))) ​value (change text to a number), of the right 4 characters of A1 should be numbers

 

[Rick Rothstein]

if your data is in cell A1,

then use data validation > Custom with the following rule;

=AND(LEN(A1)=6,ISTEXT(LEFT(A1,2)),ISNUMBER(VALUE(RIGHT(A1,4))))

then copy down through column A, or adjust as necessary.

What do you think should happen if the user types in AB3E45 or AB3OCT? Does what you thought actually happen?

 

[shg]

=product(-iserror(-mid(a1, {1,2}, 1)), -isnumber(-mid(a1, {3,4,5,6}, 1)))

 

[cooper645]

AB3E45 - Eulers number would probably pass the test, not sure on the other one. Nor am I aware how to check/counter this.

 

[gfranco]

Hello.
My contribution.
Forgive translated English.
The formula looks like this:

in data validation select "custom" and type this formula

=SUMPRODUCT(--(LEFT($D4;2)="ks");--(SUMPRODUCT(--ISNUMBER(--MID($D4;ROW(INDIRECT("3:"&LEN($D4)));1)))=4);--(LEN($D4)=6))