Validation on cell

  • Thread starter Thread starter dalymjl
  • Start date Start date
D

dalymjl

Is it possible to apply a validation to a cell to allow only a 7 digit
number followed by any letter from A to W e.g 1234567T?
 
try placing the following formula in the "formula" section of the data
validation dialog box.

=AND(LEN(A1)=8,NOT(ISNUMBER(VALUE(RIGHT(A1,1)))))
 
dan said:
try placing the following formula in the "formula" section of the data
validation dialog box.

=AND(LEN(A1)=8,NOT(ISNUMBER(VALUE(RIGHT(A1,1)))))
Click to expand...

Many thanks for you help. There is still a slight problem as that
validation would permit a letter or other character in the first 7
digits and I only want numbers in the first 7 places.

regards

MJD
 
=AND(LEN(A1)=8,ISNUMBER(--(LEFT(A1,7))),CODE(RIGHT(A1,1))>=65,CODE(RIGHT(A1,1))<=90)


the above will not allow lower case at the end, if you want that the users
to be able to enter both a and A use


=AND(LEN(A1)=8,ISNUMBER(--(LEFT(A1,7))),CODE(UPPER(RIGHT(A1,1)))>=65,CODE(UPPER(RIGHT(A1,1)))<=90)

--


Regards,


Peo Sjoblom
 
'Peo Sjoblom[_2_ said:
;716737']=AND(LEN(A1)=8,ISNUMBER(--(LEFT(A1,7))),CODE(RIGHT(A1,1))>=65,CODE(RIGHT(A1,1))<=90)


the above will not allow lower case at the end, if you want that th
users
to be able to enter both a and A use


=AND(LEN(A1)=8,ISNUMBER(--(LEFT(A1,7))),CODE(UPPER(RIGHT(A1,1)))>=65,CODE(UPPER(RIGHT(A1,1)))<=90)

--


Regards,


Peo Sjoblom

dalymjl said:
dan dungan;716380 Wrote:-

Many thanks for you help. There is still a slight problem as that
validation would permit a letter or other character in the first 7
digits and I only want numbers in the first 7 places.

regards

MJD
Click to expand...
Click to expand...


Many thanks Peo.

That works brilliantly
 
Back
Top