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

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

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.

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

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

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