| || || |
MS Excel spreadsheet changing numbers
Asked Mar 24, 2006, 04:50 AM
I have a spreadsheet where I enter credit card numbers, 16 digits. One note, I have the field setup as a custom format, that displays in the custom format as ####-####-####-####. This way it automatically adds the 4 dashes.
For some reason, it always changes the last digit to zero. For example, I enter 1111222233334567, after I hit enter, it becomes 1111-2222-3333-4560.
WHY is it doing changing the 7 to a 0??
This error, of changing the last digit to zero, only occurs when I type my data into my custom format field.
| || |
| || |
Mar 24, 2006, 06:28 AM
Just tried your problem. I get the same thing. Have you reported it to Microsoft? It also happens after 15 digits, the 16th changes to a zero??
Microsoft knowledgebase shows:
When you type a number that contains more than 15 digits in a cell, Microsoft Excel changes any digits past the fifteenth place to zeroes. For example, if you attempt to type a credit card ID number in the following format
Excel changes the last digit to a zero.
Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.
To work around this behavior, format the cell as text. The cell can then display up to 1,024 characters.
This behavior only occurs with numbers that are intended for calculation, that is, in cells that are formatted as numbers. In cells that are formatted as text, you can type up to 32,767 characters, of which Excel displays up to 1,024 characters on the worksheet.
Because custom number formats are designed to work primarily with numbers, you cannot create a custom number format that stores more than 15 digits. For example, you cannot use the following format to store a 16-character credit card ID as a number:
If you type the number 1111222233334444 in a cell that uses the ####-####-####-#### format, Excel displays 1111-2222-3333-4440 in the cell. The actual number that you are attempting to store is 1,111,222,233,334,444, which is over one quadrillion. But because this number is so large, Excel drops the last (least significant) digit, and puts a zero in its place.
| || |
| || |
Sep 6, 2012, 01:44 PM
I like to change last number by zero for e.g 456 to 450 or 53 to 50
How can I do this
| || |
| || |
Sep 7, 2012, 01:03 PM
1) Format the empty cells where these credit card numbers WILL go as text. This is critical. Once that is done, you can enter 16 digit numbers and they will be text, so they will not be changed in any way.
But, they will also be text, so no number formats can reformat them visually. For that, you can employ a macro.
2) Right-click the sheet tab and select VIEW CODE, paste in this macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim stNum As String
If Not Intersect(Target, Me.Range("A1:A200")) Is Nothing Then
If Len(Target) < 15 Then Exit Sub
Application.EnableEvents = False
stNum = Format(Target, "####-####-####-####")
Target = stNum
Application.EnableEvents = True
Edit the macro part in red to refer to the just the cells where you want 16-digit numbers reformatted.
3) Close the VBEditor and save your workbook as a Macro-Enabled workbook type.
Now enter a 16 digit code in one of those field and the macro above will "fix it"
| || |
| || |
Oct 22, 2012, 01:03 PM
Originally Posted by abdullah sardar
Will show as 450. The number after the negative sign is how many places prior to the decimal point (to the left) will be rounded down.
Add your answer here.
Check out some similar questions!
Iodine Numbers [ 5 Answers ]
Does anyone out there know how to calculate iodine numbers? :eek: If so please explain it to me like I was a two year old, then maybe I might get it!
The data I have is:
The weight of the fat sample in grams
Amount of thiosulphate required in ml
Blank-fat source titration value in ml
The Red Heifer Of Numbers 19 [ 7 Answers ]
DOES ANYONE HERE HAVE CURRENT INFORMATION REGARDING "THE SEARCH FOR THE RED HEIFER?"
I share the following for those who may not be familiar with the significance for "THE SEARCH FOR THE RED HEIFER!"
THE "RED HEIFER" SACRIFICE PRECEDING THE REBUILDING OF THE TEMPLE: The...
Possible to link a spreadsheet 2 way [ 7 Answers ]
I know how to do a "one way" link in a spreadsheet. For example, if you change the value in A1, you can make it automatically change the value in A2 also.
However, is there a way, if you change the value A2 that it will also change the value of A1? Thus, whichever you change, A1 or A2, will...
What are the next 3 numbers in the sequence? [ 1 Answers ]
Cell phone numbers [ 2 Answers ]
How can you identify who a phone number belongs to if it is listed to a cell phone? Is there a way to ID the caller or trace who the number is assigned to? I receive calls from numbers that I do not know and I have tried a reverse look-up with programs such as "Anywho" with no luck. Is anyone...
View more questions Search