Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Validation on cell
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?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation on cell
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))))) |
#3
|
|||
|
|||
Quote:
regards MJD |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation on cell
=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(UP PER(RIGHT(A1,1)))<=90) -- Regards, Peo Sjoblom "dalymjl" wrote in message ... dan dungan;716380 Wrote: 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))))) 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 -- dalymjl |
#5
|
|||
|
|||
Quote:
Many thanks Peo. That works brilliantly! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Cell Validation | Excel Worksheet Functions | |||
In Cell Validation List & Linked Cell VLOOKUP | Excel Discussion (Misc queries) | |||
Data Validation: Store cell address instead of value in the cell? | Excel Discussion (Misc queries) | |||
cell validation | Excel Worksheet Functions |