Validation - 10 digit number starting with 6211...

[Chunky_22]

I should know how to do this...

I need to use data validation to restrict entries into a cell to a 10 digit number starting with 6211. eg 6211000000

Please end my frustration and remind me how to do it!

Thanks

 

[T. Valko]

I should know how to do this...

I need to use data validation to restrict entries into a cell to a 10 digit number starting with 6211. eg 6211000000

Please end my frustration and remind me how to do it!

Thanks

Try this...

Data Validation
Allow>Custom
Formula:

=AND(LEN(A1=10),--LEFT(A1,4)=6211,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If you try that formula on the worksheet it must be array entered**.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[T. Valko]

Try this...

Data Validation
Allow>Custom
Formula:

=AND(LEN(A1=10),--LEFT(A1,4)=6211,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If you try that formula on the worksheet it must be array entered**.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Ooops! Typo

The correct formula should be:

=AND(LEN(A1)=10,--LEFT(A1,4)=6211,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

 

[Scott Huish]

How about:

Allow whole number

Between

Minimum:6211000000
Maximum:6211999999

 

[Chunky_22]

Many thanks for both replies.

 

[T. Valko]

How about:

Allow whole number

Between

Minimum:6211000000
Maximum:6211999999

Well, I'll be....