numeric data with restriction of data length

[Guest]

how can i set a cell or column value to accept only numeric data with the
restrition of 16 digit length ???

 

[Guest]

Use Data validation

Custom

Formula:

=Len(A1)=16

which will allow ONLY length of 16

 

[Rick Rothstein \(MVP - VB\)]

how can i set a cell or column value to accept only numeric

data with the restrition of 16 digit length ???

It is unclear from your post whether you want exactly 16 digits to be
entered or a maximum of 16 digits to be entered. The formula below assumes
the maximum of 16 digits (change the <= to = for exactly 16 digits).

Select the cell, cells or entire column (as appropriate), click
Data/DataValidation on Excel's menu bar, change the Allow drop-down to
Custom and use this formula in Formula field...

=AND(LEN(A1)<=16,ISNUMBER(A1))

Change the A1 reference to the address of the first cell in the range you
have selected.

Rick

 

[Gord Dibben]

The suggestions given both work but be aware that Excel will accept 15 digit
numbers only.

The 16th will be turned to zero.

1234567812345678 will become 1234567812345670

If you need the 16 digits the cell must be formatted as Text.


Gord Dibben MS Excel MVP

On Thu, 9 Aug 2007 07:50:27 -0700, kHuRRum HumaYun <kHuRRum