Excel - Exporting to a .csv file problem

  boot-it-out 13:11 21 Jun 07

I have a spread sheet ( MS Excel 2002 ) telephone directory where one of the columns contains the Phone Numbers.
The Phone numbers can be either "Internal" numbers comprising 8 digits, e.g. 12345678, or "National" Numbers which require a "9" to be dialled to get an outside line, so are listed as 901234567890 etc.
I need to export / save the phone number list as a Comma Separated Value file ( .csv) for use in another program, but when I do this and then open the .csv file all the "National" Numbers have been coverted to some sort of engineering code, and appear as "E+113456" or similar.
I have tried formatting the relevant cells as "Number" with 0 decimal places etc, but the above still happens.

Anyone know how I can save the file as a .csv file but retain the original format, without it converting to this engineering code ( or whatever it is ) ?


  VoG II 13:30 21 Jun 07

To get them to export in the desired format you would need to convert them to text by preceding them with a ' e.g.


If there is a long list this would be tedious. Say the numbers are in Column A starting at row 1 then in B1 enter


and copy the formula down. Then select column B, Copy then Edit > Paste Special and choose Values. Then delete the original column A. Save the file as a .CSV

Open the CSV in Notepad, Edit > Replace, enter ! for what to replace and leave the replace with box blank.

  boot-it-out 13:35 21 Jun 07

Thanks - I'll try that -
I don't know much about Excel - any idea why the number is being converted this way though and what it all means - the "E+11....." stuff ?


  VoG II 13:39 21 Jun 07

Excel will display any number longer than 11 digits in scientific format, e.g. 1.23457E+11, which means 1.23457 x 10 to the 11th power.

This is related to the precision with which numbers are stored internally.

  boot-it-out 13:41 21 Jun 07

Thanks VoG - All is now clear :-)

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced