Excel csv files converting long numbers to scientific notation

Well, the question is what you are willing to give up for it?

Excel will store long numbers in scientific notation because it just frankly has a limit on number length, go over that length WITH A NUMBER and it converts-has nothing to do with csv.

The reason I stress "with a number," is that there is no such limitation on text-even text that is composed of nothing but numbers (though excel will do it's little red triangle thing if you store a number as text).

If you put a single quote, aka ' , in front of your number, it will store it as text and show it normally.  But...

Now as far as excel is concerned that is not a number, but rather a text string, so you can't do any math on it.  If it is a product number or phone number or something then no problem, but if you need it to be a number-problem.

Solution (maybe)?  Have two columns, a hidden column with cells holding the number and a second display column showing the text version-but then how do you show as text? 

The solution is a lot less intuitive than it should be...

In your display cell, type =text(A2,"############################")

Why excel doesn't have a specific character to say "show this as text," I don't know, I tried "@" and it didn't work-but should have.

Oh, in the above, you need at least as many number signs (#) as you have digits in your number.  Also, if it isn't obvious, A2 is a placeholder for the cell reference you are changing to text.

Hope this helps!

Aaron

(Edit to add, for anyone who tests "@" and discovers that it works fine, try it with a long number.  Even though it should display as text, and therefore display the full number, it "helpfully," converts to sci notation anyway)

Before opening, rename the *.csv to *.txt

When you open that the Text Wizard will pop up where you decide to import the numbers as text.

Gord

Before opening, rename the *.csv to *.txt

When you open that the Text Wizard will pop up where you decide to import the numbers as text.

Gord

Well sure, if you want to do it the easy way...

Good answer.

Good answer IF the user has access and permission to make the necessary change.

Thanks for the feedback.

Gord

Gord wrote:

Before opening, rename the *.csv to *.txt

When you open that the Text Wizard will pop up where you decide to import the numbers as text.

There is no need to rename the file.  Simply click Data > From Text.  By default *.csv as well as *.txt files are displayed.

But we can also select All Files (*.*) or simply type the entire file name if it has a non-standard extension or no extension.

Sharon wrote:

"Excel keeps converting long numbers to scientific notation when open csv files.  How do I prevent Excel from doing this?"

We cannot, if you want the data to be entered as numeric values. We must change the format of the cells to Number after opening the file. Excel usually formats cells as General when it opens a CSV file; and the General format displays integers with more than 11 signficant digits in Scientific form.

Caveat:  If the long numbers might have more than 15 significant digits, Excel will only interpret the first 15 significant digits, replacing any digits to the right with zeros.  In that case, it is important to import the data as Text (see Gord's response, but ignore the suggestion to rename the file), and we cannot use the data in numeric expressions without some loss of accuracy.

Thanks for the memory jog.

I'm still using Excel 2003 and had removed the Import Text File command from the Data Menu for some reason which has its source rooted in antiquity.

I promise to use 2007 more often but for now I put the command back on the 2003 menu.

Gord