Anonymous
Anonymous asked in Computers & InternetSoftware · 1 decade ago

How do I get keep the leading 0 in a CSV file when converting from Excel

I'm converting a spreadsheet to csv, and when I do the 0 from the zip code field drops off.

I've tried defining the field as text or zip code, but when I convert to csv it drops off anyway.

I'm using the 2003 version of office. Any advice would be appreciated. Please, no profanity unless it is directly related to the solution.

2 Answers

Relevance
  • jmorge
    Lv 6
    1 decade ago
    Favorite Answer

    hmmm, very strange. I just tried it with zipcodes entered as text format, zipcode format, and a bunch of other different ways and in every case, it exported it out with the leading 0 in place. (I'm using Excel 2003 as well). There must be something very strange in the way the data was either first entered and then reformatted or something.

    Suggestion #1: copy your column of zipcodes and then do a paste special > Values into a blank column. Format that column back Text format. Then copy/paste it back to your original data. This helps force excel to set all the data to a text format.

    Suggestion #2: not an elegant solution but if this is a "one-time" export, change all zipcodes to include some "special" character that doesn't appear anywhere else. Then export it, open the file in something like Notepad and do a search/replace and remove all the special characters. For example, in some blank column enter

    ="@@@"&A1 (where A1 is whereever your zipcode is). This will turn your zipcode into something like "@@@01234". Then copy/paste special, Values back ontop of your original data and delete the formulas. After you've exported it to CSV format, open it in Notepad and do a find/replace for finding @@@ and replacing with a blank.

    I know it's not the most elegant solution, especially if you're doing this export a lot but it would at least get you around the problem for now.

  • 3 years ago

    you ought to write down a programm to do it, considering Excell by potential of it self isn't able to do the restore length by potential of itself. you are able to save as a CSV, and easily code a application to study, and finished each column of each row, with the quantity required of areas to make it mounted.

Still have questions? Get your answers by asking now.