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

Thanks Bernard.

If Excel is, indeed, 'trying to be helpful', it's not doing a good job
because its behaviour is preventing me from seeing the data as it exists in
the CSV file. If such a function is built into Excel, whether it's helpful
or not depends upon whether you can turn it on or off. It also raises the
question of how Excel determines what a 'large' number is and whether this
can be changed or not.

Not only is there nothing I can see about the original data that
distinguishes the data in question from any other numeric string, but the
cells that are displaying the value in scientific notation were already
formatted specifically as 'Text' and still are. This can be confirmed using
Format | Cells. Applying the formatting again makes no difference. In fact
if I change the formatting to something else, then change it back to 'Text'
again, it still displays in scientific notation! Surprisingly, as I've
already mentioned, selecting the cell, entering 'Edit' mode (F2), then
hitting Enter or moving the cursor to another location (or hitting 'Tab')
has the cell display the value in line with it's format (i.e. Text) without
actually editing the contents at all.

Formatting the cell as 'Numbers with 0 places' does, as you suggested, show
the full number correctly (except where the string is more than 15 digits
where the 'value' gets rounded as established earlier), albeit as a
right-aligned numeric value rather than a left-aligned text string).

With the lack of control and an apparent inability to disable it, it's
difficult to see how such behaviour can be a 'design feature' and I think I
can be forgiven for seeing it as a bug. I fully appreciate that viewing CSV
files is not Excel's primary role and that there may be some good reason for
this behaviour, but there are a large number of users who use Excel for list
management and data organisation. The apparent inability to display numeric
strings as text can be quite a severe limitation when using the program in
this way.

If you forget all the finer detail I've given here to illustrate the
problem, it comes down to this: If I have a cell specifically formatted for
text, surely any data entered into it should be displayed as a text string,
not a numeric value showing in an unspecified and, apparently unrelated,
format.

I'll send you the workbook and CSV files by email.

Thanks again for your help.

--

Plegron Snaith


"Bernard Liengme" <bliengme@xxxxxxxxxxxxxxxxx> wrote in message
news:u8HgHGPEIHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
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 ***. 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?














.