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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Of so it is NOT the 15 digit limit. It is just Excel trying to be helpful.
When it 'sees' a large number it uses scientific format since (for many)
this is easier to read and (perhaps more importantly) takes up less room
when not all 12 digits are displayed.
You just need to select this column and use Format | Cells and set the
format to Numbers with 0 decimal places.
If you still have a problem send me a sample file (my private email not the
newsgroup) and I will 'play' with it.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Plegron" <plegron@xxxxxxxxxxx> wrote in message
news:%23XtZVgOEIHA.3716@xxxxxxxxxxxxxxxxxxxxxxx
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 sheet. 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 sheet 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?












.

Relevant Pages

  • Re: Text formatted cells displaying numbers in scientific format
    ... If Excel is, indeed, 'trying to be helpful', it's not doing a good job ... distinguishes the data in question from any other numeric string, ... has the cell display the value in line with it's format (i.e. ...
    (microsoft.public.excel)
  • Re: Text formatted cells displaying numbers in scientific format
    ... When it 'sees' a large number it uses scientific format since ... I accept that Excel can't handle numbers greater than 15 digits and your ... string was 16 digits or more to be converted to a scientific value. ... It just doesn't display correctly in the cell (except after entering ...
    (microsoft.public.excel)
  • Re: significant figures
    ... For VB 6 in Excel 2003, Format does not seem to format beyond 15 significant digits. ... The reason I returned a variant is because the Format function is not limited to 14 significant digits as you have said... ... Of course, to hold the accuracy, this value must be preserved as a String. ...
    (microsoft.public.excel.worksheet.functions)
  • Excl columns in string
    ... I want the excel to store numbers as string like 0001 and not as 1. ... Set Cell format using VB.NET ... I want to open a exsiting CSV file and set the Data Format of column A (Date ...
    (microsoft.public.excel.worksheet.functions)
  • Actual solution
    ... This seems to be the EASIEST SOLUTION, and is built right into excel. ... some cells still display the numeric string in scientific ... Selecting the cell shows that it's already correctly formatted. ... What if you format ...
    (microsoft.public.excel)