WordPress.org

Support

Support » Plugins and Hacks » Export to Excel and Keep Preceding "0" for numbers?

Export to Excel and Keep Preceding "0" for numbers?

  • Hi,
    How can I export to Excel ( for both Max & PC ) and still keep the preceding “0” in my numbers.

    At the moment I loose the zeros…

    Regards

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Michael Simpson

    @msimpson

    If you look at the export CSV file with a text editor does it have the preceding 0’s? If so, try formatting the column in excel as text instead of number and see if that works.

    codinedawn

    @codinedawn

    Hi Michael, same problem, preceding zeros are dropped in the Excel CSV export but data is OK in ConTEXT.

    I just read up: “By default, Excel’s ‘general’ cell format eliminates leading zeroes.”

    The cells in the CSV exports are all in ‘general’ format. Maybe they should be in ‘text’ format, which leaves data as-is?

    Thanks, otherwise a very useful plugin.

    Plugin Author Michael Simpson

    @msimpson

    A CSV file does not specify format, only data. When you open it, Excel is then applying its default formats. There is nothing I can do in a CSV file to tell Excel how to format the data. I don’t know if there is any setting you can tweak in Excel to change what it does by default.

    codinedawn

    @codinedawn

    Thanks for following up, I understand Excel is the least of your concerns.

    I found a basic manual fix for data in Excel:

    Convert the cell format (for the column with numbers) from ‘General’ to ‘Custom’.

    In the input under ‘Type:’, put in 0000000000 (or the amount of zeroes for the telephone number). This will force numbers to have the same amount of digits – adding a preceding 0 to those without.

    For example:
    – General Cell: 123
    – Change to custom Cell and make Type: 0000
    – Data becomes 0123

    Basic fix, but hopefully helps someone.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Export to Excel and Keep Preceding "0" for numbers?’ is closed to new replies.