Excel - Exporting to a .csv file problem

  boot-it-out 13:11 21 Jun 2007
Locked

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 ) ?

Cheers.

  VoG II 13:30 21 Jun 2007

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

'901234567890

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

="!"&A1

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 2007

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 ?

Thanks

  VoG II 13:39 21 Jun 2007

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 2007

Thanks VoG - All is now clear :-)

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

Creative studio Omnibus' brand identity for We Said Enough fights back against sexual misconduct

WWDC history: Apple's product launches since 2005

Espace de stockage : comment libérer de la mémoire sur votre iPhone ?