Solved Turn off rounding in Excel

Microsoft Office excel 2007 home & stude...
July 18, 2010 at 08:21:48
Specs: Windows 7
I see many people with formatting issues and the "Precision as Displayed" function doesn't solve the problem either.

The problem is typing in a number like 4505269194986901 (not a valid card # by the way). Formatted as a Number with no decimal places excel rounds this to 4505269194986900. Changing the format to general or text changes the display to 4.50527E+15. When I click on this cell I notice in the text box that excel has once again rounded my number down to ...6900, not 6901.

I have never noticed this condition in years of excel reporting for an accounting company. What good is excel if it's going to change your #'s to whatever it wants ? Changing the precision does nothing either so how do I get my value to "stick" ? Either I'm retarded all of a sudden or this new version has me stumped. Or is it a bug ?


See More: Turn off rounding in Excel

Report •

July 18, 2010 at 09:51:16
✔ Best Answer
There is a limitation of 15 significant digits for numbers in Excel.

You could try formatting as TEXT, since it's a credit card number you probably won't be doing any type of math with it
You could separate the number into four parts, like:

 A1   B1   C1   D1
4505 2691 9498 6901


Report •

July 21, 2010 at 14:27:28
Thanks Mike, very helpful.

It sucks that Excel can't record credit card #'s as I type them. I use them occasionally to copy/paste into websites so they can't have spaces. They need to be as-is or else I have to delete spaces, dashes or whatever I add to save most of my text. I can do that however until Excel updates their software. It would be a nice add. "This is Windows 2010 and it was my idea." Add a new format for us citizens of the New Age.

Using TEXT or GENERAL doesn't work as it displays the data in the 4.5557E+15 format. It also changes the 16th digit to a zero as it did in NUMBER format which defeats the purpose of recording a credit card number this way. There just isn't a format for this...yet...

I appreciate your answer though, it pointed me to the exact issue I was frustrated with.


Report •

July 21, 2010 at 15:04:17

If you format empty cells as Text, then copy your card numbers and PasteSpecial - as Text into those cells they retain the original format - all 16 characters.

I copied your example to an Excel cell and it retained its format - and no rounding or other changes.

It also worked with leading and trailing zeros.

However it did not work for a card number separated by spaces.
I did find that pasting into the formula bar worked in that case.


Report •

Related Solutions

Ask Question