Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
HI,
I would like to limit input to all cells within a column to the following; cannot be more then 9 digits with the first two always being "NJ" and the last 7 numeric including 0. Anything other then this should generate an error message. Any help would be gratly appreciated. -- Bruce |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
Try this:
Select Col_A, with A1 as the active cell <data<validation Allow: Custom Formula: =AND(COUNTIF(A1,"NJ???????"),INT(--RIGHT(A1,7))=--RIGHT(A1,7)) Does that help? *********** Regards, Ron XL2002, WinXP "BruceY" wrote: HI, I would like to limit input to all cells within a column to the following; cannot be more then 9 digits with the first two always being "NJ" and the last 7 numeric including 0. Anything other then this should generate an error message. Any help would be gratly appreciated. -- Bruce |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
One way:
Allow: Custom Formula: =AND(LEFT(A1,2)="NJ",LEN(A1)<=11,ISNUMBER(--MID(A1,3,9))) In article , BruceY wrote: HI, I would like to limit input to all cells within a column to the following; cannot be more then 9 digits with the first two always being "NJ" and the last 7 numeric including 0. Anything other then this should generate an error message. Any help would be gratly appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
-- Bruce "Ron Coderre" wrote: Try this: Select Col_A, with A1 as the active cell <data<validation Allow: Custom Formula: =AND(COUNTIF(A1,"NJ???????"),INT(--RIGHT(A1,7))=--RIGHT(A1,7)) Does that help? *********** Regards, Ron XL2002, WinXP "BruceY" wrote: HI, I would like to limit input to all cells within a column to the following; cannot be more then 9 digits with the first two always being "NJ" and the last 7 numeric including 0. Anything other then this should generate an error message. Any help would be gratly appreciated. -- Bruce |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
Hi Ron,
This worked GREAT. Thanks so much for your help and for your quick response. -- Bruce "Ron Coderre" wrote: Try this: Select Col_A, with A1 as the active cell <data<validation Allow: Custom Formula: =AND(COUNTIF(A1,"NJ???????"),INT(--RIGHT(A1,7))=--RIGHT(A1,7)) Does that help? *********** Regards, Ron XL2002, WinXP "BruceY" wrote: HI, I would like to limit input to all cells within a column to the following; cannot be more then 9 digits with the first two always being "NJ" and the last 7 numeric including 0. Anything other then this should generate an error message. Any help would be gratly appreciated. -- Bruce |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation
You're very welcome, Bruce....(and thanks for the feedback!)
*********** Regards, Ron XL2002, WinXP "BruceY" wrote: Hi Ron, This worked GREAT. Thanks so much for your help and for your quick response. -- Bruce "Ron Coderre" wrote: Try this: Select Col_A, with A1 as the active cell <data<validation Allow: Custom Formula: =AND(COUNTIF(A1,"NJ???????"),INT(--RIGHT(A1,7))=--RIGHT(A1,7)) Does that help? *********** Regards, Ron XL2002, WinXP "BruceY" wrote: HI, I would like to limit input to all cells within a column to the following; cannot be more then 9 digits with the first two always being "NJ" and the last 7 numeric including 0. Anything other then this should generate an error message. Any help would be gratly appreciated. -- Bruce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation | Excel Worksheet Functions | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |