Last digit changes using data validation
Closed
BmcC
-
Sep 18, 2009 at 08:02 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 18, 2009 at 11:41 PM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 18, 2009 at 11:41 PM
Hello,
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.
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.
Related:
- Last digit changes using data validation
- Samsung mobile reset code 8 digit - Guide
- Display two columns in data validation list but return only one - Guide
- Digital data transmission - Guide
- Tmobile data check - Guide
- Please wait a few minutes to receive your 6-digit code before trying again. - Facebook Forum
1 response
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Sep 18, 2009 at 11:41 PM
Sep 18, 2009 at 11:41 PM
Hello
I think you have to restrict the input to ONLY digits.
Format the cell as TEXT and put the validation as =ISERROR(VALUE(CELL ADDRESS HERE))=FALSE
I think you have to restrict the input to ONLY digits.
Format the cell as TEXT and put the validation as =ISERROR(VALUE(CELL ADDRESS HERE))=FALSE