Re: 'Text' formatted cells displaying numbers in scientific format

Tech-Archive recommends: Speed Up your PC by fixing your registry

Thanks Bernard.

I accept that Excel can't handle numbers greater than 15 digits and your
explanation has helped shed some light on the problem, but with this in mind
I have done some further investigation and it's raised some interesting
points:

1. I have no control over the source data as it comes from my bank's
website, but I have the option to either open the CSV file or save it. The
results are the same if I open the file as if I save it then open it.

2. I'd expect any 'numbers only' numeric string to be treated as a numeric
value and strings that included spaces or letters to be treated as text.
This does, indeed, happen as can easily be seen because numbers are right
aligned.

3. Given what you've said, I'd then expect any numeric value where the
string was 16 digits or more to be converted to a scientific value. This is
the case, but I can see two 12 digit numbers that have also been converted
(e.g. 483036401001 displaying as 4.83036E11). Why is this?

4. I have also opened the CSV file (that was saved directly from the site)
in a text editor to see if there's anything peculiar about these strings,
but they are just 12 digit numbers.

I thought a CSV file was just plain text, but could there be some other data
or attribute that's affecting the way these numbers are treated?

--

Plegron Snaith


"Bernard Liengme" <bliengme@xxxxxxxxxxxxxxxxx> wrote in message
news:uIf2wJNEIHA.536@xxxxxxxxxxxxxxxxxxxxxxx
The precision limit means Excel cannot store numbers with more than 15
digits
What you have are not really 'numbers' - you do no arithmetic with them -
they are just strings of digits and as such they might as well be treated
as text.
Adding a single quote (which is neither displays nor printed) makes Excel
teat it as text. But you cannot add the single quote after importing since
Excel has already lost the last few digits.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Plegron" <plegron@xxxxxxxxxxx> wrote in message
news:O7%23uQgCEIHA.2004@xxxxxxxxxxxxxxxxxxxxxxx
Thanks, Bernard, but I don't see how the 15 digit precision limit has any
bearing on this.

No calculation has been performed and the data and the value in the CSV
file is fine after download and saving. It's also fine when pasted (Copy
> Paste
Special > Values) into the ALREADY FORMATTED ***. If the cell is
selected, the correct value displays in the editing field at the top. It
just doesn't display correctly in the cell (except after entering editing
mode as previously described).

I accept that putting an inverted comma at the beginning of the string
would work, but then just going to edit the cell fixes the problem
without adding the comma. I can't see any other way to '..arrange to
have a single quote in front..'. Did you have something in mind?

This isn't really a work-around and doesn't begin to explain the cause of
the problem, but I have seen this behaviour before and it would be good
to find out what causes the problem and how it might be avoided/fixed.

--

Plegron Snaith

"Bernard Liengme" <bliengme@xxxxxxxxxxxxxxxxx> wrote in message
news:%23sdUXJBEIHA.3400@xxxxxxxxxxxxxxxxxxxxxxx
Excel has a 15 digit precision limit
Try formatting the receiving cell as Text before importing
Or arrange to have a single quote in front of the long string of digits
before the import
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"plegron" <plegron@xxxxxxxxxxx> wrote in message
news:eZ43S0AEIHA.4332@xxxxxxxxxxxxxxxxxxxxxxx
I use Excel to view statement information downloaded from my bank in CSV
format.

As some fields have long numeric strings that Excel displays in
scientific notation (4988243043081440 displays as 4.98842E+15), I use
Copy > Paste Special > Values to copy the data to a new *** which is
correctly formatted in order to view the data correctly as text.

Despite this, some cells still display the numeric string in scientific
notation. Selecting the cell shows that it's already correctly
formatted. Applying the change again or changing it then changing back
makes no difference, however opening the cell for editing (F2) then
exiting forces the cell to display correctly.

What's going on and why does it affect some cells and not others?

How can I prevent this from happening?










.

Quantcast