Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004

    Question Unanswered: excel formatted file

    I created a .csv file for import to Excel. (Prefer spreadsheet to automatically open and recipient not have to use the Wizard.)
    One of the fields in the .csv file is an Item Code Number, which is numeric and can have leading zeroes.

    To avoid truncation of leading zeroes when the spreadsheet opens, in the .csv file, I tried putting a single quote infront of the value (i.e. '000485) and also tried double quote/single quote/double quote around the value (i.e. "'000485"). Same result in both cases. Leading zeroes were preserved but the single quote was visible in the Cell (i.e. '000485). I don't want the single quote to be visible in the cell. I want 000485 only to be displayed.

    Has anyone had success in accomplishing this?

  2. #2
    Join Date
    Oct 2003
    Putting the single quote (') in the cvs file does not help - Excel will import it as that. Rather the quote has to be entered into the saved Excel file.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Thanks for your reply. Is there anything I can do in the .csv file to make Excel recognize/treat this numeric value as a text field?

  4. #4
    Join Date
    Aug 2003
    Phoenix, AZ
    Only way I've been able to get around your issue (I've run into it too), is to create a Macro to import the file to an empty Excel file. The macro would run the wizard for you. You can save it in CSV format, but delete the CSV extension (or save a a txt or prn file). Then in the macro, you can designate the column as a text column. In the file you originally used to create the CSV, PRN or TXT file, custom format the column cells. (Format Cells, Select "Custom", then click on the "0" below general. In the "TYPE" field, where the single 0 now appears, key in as many zeros as you want to have in the field.). WHen the file is imported and that column designated as text column, it then will appear as 0000123 instead of 123. Or you can do the formatting as part of the Macro once it's in Excel. Hope this helps.
    Last edited by Dcutler; 02-04-04 at 15:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts