LinkBack URL
About LinkBacks
Hi,
I have a bunch of cells with the, customized format: ####-####-####-####
For whatever reasons, the last digit is being changed is being changed to '0'
Why is this & more importantly - How to prevent?
Thanks
####-####-####-#### - Last Digit Changes to '0'
Hi,
I have a bunch of cells with the, customized format: ####-####-####-####
For whatever reasons, the last digit is being changed is being changed to '0'
Why is this & more importantly - How to prevent?
Thanks
Last edited by mycon73; 11-22-2017 at 12:34 PM.
MyCon
-- Using Latest Version of Excel
Excel stores numbers as double precision, so it can only store numbers to about 15 digits (truncating additional digits). Solution is to store 16 digit or longer numbers as text strings and hope you don't need to do any calculations on them. This also means that you cannot use number formatting to "add" hyphens or other characters to the displayed number or stuff like that.
My first thought is to get in the habit of including the "-" when you enter the number.
If you don't want to do that, or the data are being read from a text file of some kind, you will need a formula (MID()) to pull the four character chunks and concatenate in the "-".
Originally Posted by shg
I am assuming that the cells that you are trying to format are numeric.
Excel can only keep up to 15 significant digits. Notice that the 16th digit is the one changing to 0.
See here for more:
https://support.microsoft.com/en-us/...mbers-in-cells
Hi MrShorty,
Thank for the quick feedback. Not sure what Double Precision is
These "####-####-####-####" numbers are for Credit or Gift Cards because I'm tracking amounts given of how much money I save by earning FREE electricity
Therefore, no calculations needed
What's the solution to prevent last digit(s) to changing to '0'?
Change formatting from General or Number to Text.
Hi
Using Custom Format, "####-####-####-####'
That custom format is attempting to take a numeric value and "custom format" it to show as ####-####-####-#### but the 16 digit number (which Excel can't handle precisely) will still be the value of the cell.
Change the formatting of the cells from your Custom formatting to Text and then type in the credit card digits with the dashes exactly how you want them to look.
e.g. format as Text and then type in 1234-5678-9012-3456
Wow!
That's the only way Excel can handle this!!??
OK....
Thanks for the assistance...
Glad we could help.
When you think about it, it makes sense that something like a credit card number, order number, ID, etc. should not be inputted as an actual number but rather as text with numeric digits in it.
For example, it wouldn't make sense to say "1111-1111-1112-1111 is 10,000 more than 1111-1111-1111-1111" because credit card numbers do not have a numeric value.
Also, it is important to understand the difference between a cell's formatting and the actual value that is in the cell.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks