Why does Excel keep changing number format

Question

I have an excel file with over 800 locations and the map coordinates for each location. This is being used to create a kml file to import into a Bing map.

This morning, getting ready to finish the kml file and do the import, I found that some (most:() of the longitude coordinates had been changed from 6 digits after the decimal, ie. 24.198032, to a scientific number, something like this 4.68E + 08. the latitudes are fine, for some reason. Both columns were formatted as number with 6 decimalsWhen I try to change this back to the original format, Excel comes up with something completely useless like 999999999 or so, meaning I have to start all over again, providing I can stop this from happening again. Can anybody tell me why this happens and what I can do to stop it from repeating this?

Thanks

Eleanna

Answer 1

PS....  I wrote:

If you could provide an example Excel file that demonstrates the problem, it might go a long way toward someone helping you quickly.

You probably also need to provide some explanation of the history of the data.  For example:

• How did you enter the data into the Excel file initially?  For example, did you download a CSV file?  Or did you copy-and-paste from a web page?
• If the data ever looked correct, what did you do between then and now, when the data looks incorrect?  For example, did you change cell formats?  Did you save the file as a non-Excel workbook (i.e. something other than XLS-something file)?  If so, how did you re-enter the data into Excel?
• AFAIK, Excel does not recognize KML files per se.  However, I believe a KML file is an XML with a particular schema.  If that is correct, you might need to provide the XML schema, or explain how you save and re-enter the KML file.

My suspicion is:  the data underwent some transformation from and into an Excel workbook (i.e. an XLS-something file).  If that is correct, merely looking at an example Excel workbook might not be sufficient in order to help you.

Answer 2

Eleanna wrote:

I have an excel file with over 800 locations and the map coordinates for each location.

Eleanna wrote:

the lon coordinate for a particular place is 24.139709. Excel shows this as 4.2E+08, and when I expand the column it show it as 419999999, which makes absolutely no sense to me, or bing maps for that matter...

Excel has changed 597 out of 812 coordinates in this way, making life extremely difficult.

If you could provide an example Excel file that demonstrates the problem, it might go a long way toward someone helping you quickly.

Upload an example Excel file that demonstrates the problem to a file-sharing website, removing or changing any private data first.

Then post the "shared", "public" or "view-only" link (aka URL; http://...) in a response here.

The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files

Windows Live Skydrive: http://skydrive.live.com

MediaFire: http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

RapidShare: http://www.rapidshare.com

Answer 3

Hi,

thanks for getting back. I'm afraid it doesn't help, sorry. this is what happened (again)

the lon coordinate for a particular place is 24.139709. Excel shows this as 4.2E+08, and when I expand the column it show it as 419999999, which makes absolutely no sense to me, or bing maps for that matter...

Excel has changed 597 out of 812 coordinates in this way, making life extremely difficult. All the long coordinates start with the digits 24 and then the decimal.  I have tried using a different column, and indeed started all over again in a clean, new worksheet. For some reason, Excel does not like lon coordinates and does horrible things to them, and me...

Answer 4

An experiment... the next time this happens, try widening the column with the scientific notation... do all the digits appear in the cell now? If so, save the workbook with the new, wider columns and see if that straightens the problem out.