Skip to main content
Question

"save as spreadsheet" erasing my leading zeros

  • November 4, 2019
  • 0 replies
  • 243 views
I have a Text field that contains numeric account numbers that generally start with Zero  (ie:  04201345).  They show fine in Quickbase, but when i "Save as Spreadsheet", the account number field always erases the leading 0, (IE: 4201345)

Is there a way to make sure those Zero's stay all the time?

------------------------------
Jamie Brown
------------------------------
This topic has been closed for replies.

  • BabiPanjikar
  • Registered
  • November 4, 2019
Hi Jamie,

This is a excel behaviour and not a quickbase download issue. Do not double click the csv file directly to open in excel. This will trim the trailing zero and treat it as number instead of text. 
Try this one. close the excel and open again. Open the file using menu-file-open file, locate your csv and the open.

------------------------------
Babi Panjikar
------------------------------
    Forum|alt.badge.img+15
    • DonLarson
    • Registered
    • November 4, 2019
    Jamie,

    This is very frustrating and is a Microsoft problem not Quick Base.   You need to import the .csv into a workbook.

    Then you will need to transform the data column to Text to get the prevailing zeros.

    It is a real pain.

    Don


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
      EverettPatterso
      Forum|alt.badge.img+10
      Make a formula text field and use that on the report you want to Save as Spreadsheet

      formula text - combine single quote with original account number
      "'" & [Textfield]​


      If you add a single quote to beginning of the text string that contains a number, Excel will show the leading zeros




      ------------------------------
      Everett Patterson
      ------------------------------
        EmberKrumwied
        Forum|alt.badge.img+13

        I know this is an older post, but thought I would provide my solution for those still looking.  I have this issue as well and this is how I have solved the problem.  Download your data and then change the file format from .csv to .txt.  Open excel, then open the .txt file.  The program will take you through a wizard where you can separate the data out into columns and then set the column format (for your leading zero data) to text.  Then finish the wizard.  All your data will be in columns and you will now see the leading zeros.



        ------------------------------
        Ember
        ------------------------------
        Ember
        Terms & ConditionsAccessibility statement