16 digit number converting to scientific

sburkhar

Active Member
Joined
Oct 4, 2006
Messages
363
Why do 16 digit numbers convert? Is there a way to format the cell rather than adding the ' in front of the number to stop the conversion?
This number 4.50542E+15 should look like 4505420254200425, but when I add the ' in front of the number the last digit changes to zero 4505420254200420. Every time we miss this, it costs us money.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Excel will store only 15 digits of a number.
If you type '4505420254200425 you should get the text "4505420254200425" as text but doing arithmetic with it will fail. Or be inaccurate.
 
Upvote 0
You could change the format of the cell to Text before you put the value in the cell.

Mind you that would probably mean you would need to convert it to a number if you are using it in any calculations.
 
Upvote 0
Thank you. I was trying to avoid converting back and forth from text to number. But, I guess that's what we'll continue doing. I was just wondering why it happened.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top