I have a form that contains credit card numbers. I have set up a validation
block to hold those numbers, however, Ive run into a problem with Excel
simply changing the last digit. IE: 4000120078135436, becomes
4000120078135430. Ive tried setting the validation up thusly:

Allow whole number 0
Allow whole number 9
Allow whole number between 0, 9999999999999999
Allow whole number between 0, 99999999999999999
Allow whole number between 0, 0000000000000000
Allow Any Value, with the cell formatted to Custom, 0000000000000000

Nothing is keeping the number the way it was originally typed. If I set the
validation to text, it will alleviate the problem of the number being right,
however it will defeat the purpose of creating a cell to hold only numbers.
(BTW: the card number is bogus.)

Excel doesn't allow numbers with more than 15 significant digits.

You have to enter the CC number as TEXT.

Format the cell as TEXT.

Use Custom data validation and use this formula:

=AND(LEN(A1)=16,ISNUMBER(-MID(A1,ROW(INDIRECT("1:16")),1)))

--
Biff
Microsoft Excel MVP


"Jamie" wrote in message
...
I have a form that contains credit card numbers. I have set up a validation
block to hold those numbers, however, I've run into a problem with Excel
simply changing the last digit. IE: 4000120078135436, becomes
4000120078135430. I've tried setting the validation up thusly:

Allow whole number 0
Allow whole number 9
Allow whole number between 0, 9999999999999999
Allow whole number between 0, 99999999999999999
Allow whole number between 0, 0000000000000000
Allow Any Value, with the cell formatted to Custom, 0000000000000000

Nothing is keeping the number the way it was originally typed. If I set
the
validation to text, it will alleviate the problem of the number being
right,
however it will defeat the purpose of creating a cell to hold only
numbers.
(BTW: the card number is bogus.)

You need to use text. Excel numbers are limited to 15 significant figures.
--
David Biddulph

"Jamie" wrote in message
...
I have a form that contains credit card numbers. I have set up a validation
block to hold those numbers, however, I've run into a problem with Excel
simply changing the last digit. IE: 4000120078135436, becomes
4000120078135430. I've tried setting the validation up thusly:

Allow whole number 0
Allow whole number 9
Allow whole number between 0, 9999999999999999
Allow whole number between 0, 99999999999999999
Allow whole number between 0, 0000000000000000
Allow Any Value, with the cell formatted to Custom, 0000000000000000

Nothing is keeping the number the way it was originally typed. If I set
the
validation to text, it will alleviate the problem of the number being
right,
however it will defeat the purpose of creating a cell to hold only
numbers.
(BTW: the card number is bogus.)

Unfortunately, Excel cannot contain a 16 character number in one cell. An
alternative is to split the number into either 4 digits in 4 cells or 8 in 2
cells. though, changing to text may be the simplest idea.

"Jamie" wrote:

I have a form that contains credit card numbers. I have set up a validation
block to hold those numbers, however, Ive run into a problem with Excel
simply changing the last digit. IE: 4000120078135436, becomes
4000120078135430. Ive tried setting the validation up thusly:

Allow whole number 0
Allow whole number 9
Allow whole number between 0, 9999999999999999
Allow whole number between 0, 99999999999999999
Allow whole number between 0, 0000000000000000
Allow Any Value, with the cell formatted to Custom, 0000000000000000

Nothing is keeping the number the way it was originally typed. If I set the
validation to text, it will alleviate the problem of the number being right,
however it will defeat the purpose of creating a cell to hold only numbers.
(BTW: the card number is bogus.)