Excel Rounding Issue

A

AccentStripe

it doesnt work. entered formula 0000"/"0000 "TO" 0000"/"0000. entered
"1786000018451476". displays as 1786/0000 to 1845/1470. excel is rounding
and i dont want it to.
 
P

Peo Sjoblom

I assume you mean custom format as opposed to formula, regardless Excel
truncates all numbers after 15 digits that is why you get 1470 in the end
Excel has 15 digits precision so you cannot use custom formatting and
numbers to display this, you need to enter the lot as text without any
formatting
 
A

AccentStripe

is there a way "around" this? i have six million numbers to enter and it is
just so much easier to enter as a format.
 
P

Peo Sjoblom

Yes, you could use 2 columns and a formula, first format let's say column A
as text, then type or paste in the 16 digit numbers. Let's say the first
value is in A2,
In column B formatted as General in B2 use

=LEFT(A2,4)&"/"&MID(A2,5,4)&" TO "&MID(A2,9,4)&"/"&RIGHT(A2,4)

copy down the formula as long as needed


finally select all formula entries in B and copy them, then do edit>paste
special as values in place
Then delete column A
 
Top