This can be resolved in 2 ways.
1. When opening the csv file - rename it to .txt and open as comma delimited. Excel then prompts you to format the columns any way you like - format the long number column as text (later you can reformat as 0 decimal number and refresh and process any way you like if necessary but I understood that you need to see it as text initially).
2. In some cases this is not possible (for example if you receive a spreadsheet in Excel format that has been converted from CSV by someone else and saved with the column in the wrong format - which happens to me all the time). Select the column you wish to correct, format as text, then go to Data on the ribbon (in Excel 2007 - principal is the same in older versions though) and click "Text to columns". This does the same as the comma separated import (make sure not to use a number as a delimiter etc etc) an allows you to reformat the whole column.
plegron wrote:
'Text' formatted cells displaying numbers in scientific format
16-Oct-07
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?
Previous Posts In This Thread:
'Text' formatted cells displaying numbers in scientific format
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?
Excel has a 15 digit precision limitTry formatting the receiving cell as Text
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
Thanks, Bernard, but I don't see how the 15 digit precision limit has any
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
I see what you mean.
I see what you mean. If it's in Scientific Notation in the copy from
cell, it's Scientific Notation in the copy to cell. What if you format
the Copy From cells to no-decimal place numbers, then the copy/paste to
text formatted cells?
Beege
Plegron wrote:
Not quite, Beege.The string is not in scientific notation in the source cell.
Not quite, Beege.
The string is not in scientific notation in the source cell. It's a numeric
string downloaded in a CSV file, but for some inexplicable reason it
displays in this format in the cell it's copied to despite the format of the
cell being 'Text'.
I appreciate any help, but it's frustrating that the problem appears to be
have been misunderstood. I tried to describe it accurately and, having
re-read my post, I can't see how I can make it any clearer.
Thanks all the same.
--
Plegron Snaith
The precision limit means Excel cannot store numbers with more than 15
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
Thanks Bernard.
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
Of so it is NOT the 15 digit limit. It is just Excel trying to be helpful.
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
Thanks Bernard.
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
Re: 'Text' formatted cells displaying numbers in scientific format
A suggestion:
It may be that Excel is "trying to be helpful" when importing the CSV file.
Instead of importing the file as "csv", I would suggest you change the file
type to "txt" (Rename the file from whatever.csv --> whatever.txt).
Then do the import using the Data/Text-to-Columns Wizard.
When you get to the column that has these long digit strings, in Step 3 of the
wizard, indicate that the Column Data Type is Text.
--ron
Thanks for the suggestion you emailed me after looking at the files I sent you.
Thanks for the suggestion you emailed me after looking at the files I sent
you.
This procedure is much more involved than the simple Copy/Paste Value and,
unfortunately, it's not really worth it for the task I was doing when I
found the problem (just a daily checklist of transactions to enter). I'll
just put up with the error as it only occurs in a column I'm not using.
However, it isn't a wasted effort as I remember having this problem when
using Excel 97 to collect statement information from an on-line page (before
QIF download was widely available) and I'm sure it'll come in handy again.
Although your procedure does work around the problem, it doesn't explain the
behaviour (described in detail in my last post) and it still seems bizarre
that data pasted into a cell that's already been specifically formatted
should fail to display in that format. I've given some good arguments as to
why I believe it's incorrect to assume it's 'by design' and that without any
control or even an understanding of what's going on, it's far from
'helpful'.
If we could get some definitive comment from somebody who knows, we may find
out what's going on. Until then it's a bug as far as I'm concerned.
--
Plegron Snaith
Thanks Ron.
Thanks Ron.
Bernard gave me this workaround after looking at the files I'd sent him by
email.
The procedure works OK, but I'd still like to know why Excel behaves in this
odd way. See my last reply to Bernard above.
--
Plegron Snaith
'Fix' for text formated a Scientific Notation
I have had this same problem for a while just put up with it as well. As stated, pressing F2 to edit the cell and then ENTER 'corrects' the issue, but it is not viable if you have many cells (like the 2,200 I just fixed).
The import method works 'IF' you have the file outside Excel, but my files are in Excel and when saved to other formats they revert to the Scientific Notation.
I did, however, find a 'fix' once the data in in Excel and the column is formated as Text. All you need to do is format the values to Uppercase (I know they are numbers, but it works).
The easiest way to do this is to create a macro. Here is the code I used (change the range to the desired cells)...
Sub fixtext()
Range("A1:A2200").Select
For Each cellObject In Selection
With cellObject
.Formula = StrConv(cellObject, vbUpperCase)
End With
Next
End Sub
Submitted via EggHeadCafe - Software Developer Portal of Choice
Java link favorites
http://www.eggheadcafe.com/tutorial...ef-b0db-5368e3f8f689/java-link-favorites.aspx