2 Replies Latest reply on Apr 4, 2021 10:28 PM by Syed Aziz

    How do I do to make sure that Excel always shows the zeros?

    Carlos Fabregas New Member

      I'm generating a CSV file with the following structure:

       

      ID              UNDCAP   SUBACCOUNT

      3308799    05              000005

       

      When I open it in Excel the column UNDCAP and SUBACCOUNT appears without zeros... How do I do to make sure that Excel always shows the zeros?

       

      Do I have to change the etl? or the problem is it only with excel?

       

      Thank's a lot.

        • 1. Re: How do I do to make sure that Excel always shows the zeros?
          Priyanka Kshirsagar Seasoned Veteran

          Hi Carlos,

           

          You are not able to see the leading zeros due to the Excel setting. Please verify the output content by opening file in any text editor.

           

          Regards,
          Priyanka

          • 2. Re: How do I do to make sure that Excel always shows the zeros?
            Syed Aziz Guru

            Hello Carlos,

             

            You can add "Custom Format" Leading Zeros in Excel by the steps below:

             

            1. Select the range of cells you want to add leading zeros to and open up the Format Cells dialog box.

                 - Right click and choose Format Cells.

                 - Use the Ctrl + 1 keyboard shortcut.

            2. Go to the Number tab.

            3. Select Custom from the category options.

            4. Add a new custom format in the Type input. If you want the total number of digits including any leading zeros to be 6 then add 000000 as the custom format.

            5. Press the OK button.

             

            After applying the custom format you will notice the data has not actually been changed.  Selecting a cell with formatting will still show the original number in the formula bar. They only appear in the worksheet with the leading zero format.  This also means if you re-import the data as values, you will lose the leading zeros.

             

            Alternatively, you could force Excel to enter a number as text by using a leading apostrophe.  This means you’ll be able to keep those zeros in front as you’re entering your data.  This method is quick and easy while entering data. Just type a ' character before any numbers. This will tell Excel the data is meant to be text and not a number.  When you press Enter, the leading zeros will stay visible in the worksheet. The ' will not be visible in the worksheet, but is still there and can be seen in the formula bar when the active cell cursor is on the cell.

            Best regards,
            Syed