Excel mistakenly formats as Scientific Notation

**JBme** · 08-31-2007, 03:02 PM

How do I turn off the auto formatting to scientific notation in Excel? Our users are receiving email attachments from a vendor’s mainframe in .csv format. The csv file has quotations around all text fields but this doesn’t help. When the users try to open the file, it automatically formats some of the data as scientific notation. The data being corrupted is either a FedEx tracking # like “006854230175290” or a vendor’s part number that has an E in it like “0636107208E001". Has anyone found an option in Excel or registry key that disables auto formatting to scientific notation when opening a csv file?

**Paul** · 08-31-2007, 03:34 PM

Rather than opening the CSV file from Excel, have you tried using the Import wizard and importing those fields as text? This will preserve their formatting.

**JBme** · 09-06-2007, 10:14 AM

The users are receiving these files from a vendors mainframe and just double-click to open the files. 90% of the time the files usually don't have any data corrupted by auto formatting, so there is no way that we are going to be able to teach them to save the file and import it. We would love to find a way to stop Excel from auto formatting.

**Mission1010** · 05-29-2008, 09:13 PM

Did you ever get a resolution on this? I have the same problem. When I looked up help with Microsoft Online help, there was a note that makes me think that we can't convert to a regular number if our scientific number was originally more than 15 digits long...which mine is.

I am waiting to hear back from the vendor that supplies the CSV file to see if they have a solution.

**mikerickson** · 05-29-2008, 10:44 PM

Walkenbach lists WorkbookBeforeXMLImport as an application event.

He also notes that it isn't supported on my Mac.

Working in Windows, one might use this event to intercept the incoming data and perform an Import rather than a Paste.

**JBme** · 06-04-2008, 03:37 PM

No solution yet. Trying to get vendors to send info in Excel format instead of .csv. Not much luck yet.

Originally Posted by Mission1010

Did you ever get a resolution on this? I have the same problem. When I looked up help with Microsoft Online help, there was a note that makes me think that we can't convert to a regular number if our scientific number was originally more than 15 digits long...which mine is.

I am waiting to hear back from the vendor that supplies the CSV file to see if they have a solution.