A Complete Guide on How to Pad Zeros in Excel?

Method 1 – How to Pad Leading Zero with Custom Format in Excel?

  • Select data range D5:D20 >> Press Ctrl + 1 >> Format Cells dialog box will appear.
  • Choose the Custom option from the Category drop-down list under the Number tab.
  • Set the format as you want. We insert 000-000-0000 in the Type box >> Press OK.

Using Custom command to pad zeros

  • You will be able to format a number as a phone number keeping the leading zeros.

Keeping leading zero by using Custom command


Method 2 – How to Convert Number to Text to Pad Zeros in Excel?

  • Select the data range D5:D20.
  • Press Ctrl + 1 >> Format Cells dialog box pops up >> Select Text option from Category drop-down list under the Number tab.

Selection of the Text format to convert numbers to text

  • You will be able to convert numbers to text to pad zeros.

Converting Number to Text to pad leading zeros


Method 3 – How to Use Apostrophe to Pad Leading Zero in Excel?

We can use the Apostrophe(‘) symbol to pad leading zeros in Excel. If we insert an apostrophe before a number, the number will convert into text while keeping the leading zeros.

We inserted numbers with the leading apostrophe in the Phone Number column to get number data.

Inserting apostrophe before a number


Method 4 – How to Use the Ampersand Symbol to Pad Zeros in Excel?

  • Write down the following formula in cell F5 to show leading zeros. AutoFill the formula to the rest of the cells in column F.
="00"&C5

Using Ampersand Symbol to show leading zero


Method 5 – How to Use Excel’s Built-in Functions to Pad Zeros?

5.1 Use TEXT Function to Pad Zeros in Excel

You can use the TEXT function to pad leading zeros in Excel. It converts texts to numbers with a predefined number format.

  • Type the formula based on TEXT function cell F5 and AutoFill the formula.
=TEXT(C5,"00A0-0000")

Applying TEXT function to pad leading zeros in Excel


5.2 Combine TEXT and REPT Functions to Pad Zeros

Also combine the TEXT and REPT functions to pad zeros in Excel.

  • Insert the following formula in cell F5 and AutoFill the formula.
=TEXT(C5,REPT("0",8))

Combining TEXT and REPT Functions to pad zeros

Formula Breakdown

=TEXT(C5,REPT("0",8))

  • The REPT function will repeat zero (0) eight times and give the output as 00000000.
  • The TEXT function pads leading zeros with the number containing cell C5. The final output is 00047275.

5.3 Apply TEXTJOIN Function to Insert Leading Zeros

You can also use the TEXTJOIN function to insert leading zeros in Excel. This function adds zeros to the existing number.

  • Insert the following formula in cell F5, and AutoFill the formula.
=TEXTJOIN("",,"000",C5)

Applying TEXTJOIN Function to insert leading zero in Excel


5.4 Use the RIGHT Function to Pad Zeros in Excel

To pad or add zeros in Excel, you can use the RIGHT function. This function adds zeros from the right side based on position.

  • Write down the following formula in cell F5, and AutoFill the formula up to cell F22.
=RIGHT("00"&C5,6)

Using RIGHT function to pad leading zero in Excel


5.5 Use CONCATENATE Function to Add Leading Zeros in Excel

You can also use the CONCATENATE function to add leading zeros before a number or text.

  • Insert the following formula in cell F5, and AutoFill the formula.
=CONCATENATE("00",C5)

Use CONCATENATE function to pad leading zero in Excel

Note:
Also can use the CONCAT function to pad zeros. To do that, you can use the following formula.

=CONCAT("00",C5)

5.6 Combine REPT and LEN Functions to Pad Zeros in Excel

To pad leading zeros in Excel, we can use the REPT and LEN functions.

  • Type the formula in cell F5, and AutoFill the formula.
=REPT(0,7-LEN(C5))&C5

Combine REPT and LEN functions

Formula Breakdown

=REPT(0,7-LEN(C5))&C5

  • LEN(C5) returns the total number of characters in cell C5.
  • REPT(0, 7-LEN(C5)) is applied to insert the necessary zeros. To determine the number of zeros to add, subtract the string length in cell C5 from the maximum number of zeros.

5.7 Use BASE Function to Pad Zeros in Excel

Another function you can apply to insert leading zeros in Excel is the BASE function.

  • Type the following formula in cell F5, and AutoFill it.
=BASE(C5,10,7)

Using BASE function to insert leading zeros


Method 6 – How to Apply DAX Formula in Pivot Table to Add Leading Zeros?

  • Go to Insert tab >> Choose the PivotTable option from the Tables group.

Selection of the PivotTable feature

  • Do like the screenshot below.

Select location of the Pivot Table

  • Place the cursor over the Range menu and right-click the mouse button to get options >> Press Add Measure.

Choose Add Measure option

  • Then name the Measure according to your choice and place the formula in the Formula option.
  • Press OK.
=CONCATENATEX(Range,FORMAT([ID],"0000000"),",")

Use CONCATENATEX formula to pad leading zeros

  • You will be able to add leading zeros to your selected cells.

Insrting Leading zeros


Method 7 – How to Use Power Query to Pad Zeros in Excel?

  • Go to the Data tab >> Select From Text/CSV option from the Get & Transform Data group.

Use Get & Transform Data group

  • Import your CSV After importing your CSV file, you will see a preview of your data, like the following picture.
  • Click on Transform Data.

Selection of the Transform Data option

  • A Power Query Editor window will be open on your worksheet as shown in the image given below.
  • Click on the marked area in the ID column >> Choose the Text option.
    Choose the Text option

    Click the image to view it in full size

  • A Change Column Type dialog box will open and choose the Replace Current option.

Select Replace Current option

  • You will be able to add zeros in the ID column as shown in the following image.

Adding leading zeros

  • For loading data back to Excel workbook click on Close & Load.

Select the Close & Load option

  • You have successfully imported your CSV file into Excel and kept the leading zeros intact.

Imported CSV file into Excel and kept the leading zeros


How Many Ways to Remove Leading Zero in Excel?

Method 1 – How to Use Cell Format to Remove Leading Zero in Excel?

You need to change the cell format to remove leading zeros in Excel.

We added leading zeros in a number using the Custom format. Now, convert the cell format to the default General format to remove leading zeros.

Converting Custom format to General format

Click the image to view it in full size


Method 2 – How to Use Convert Text to Number Feature to Remove Leading Zero in Excel?

You can easily remove the leading zeros by converting the Text to Number format. If cells are formatted in Text, the leading zero will be shown in Excel with an error notification.

  • Click the exclamation point showing due to error >> Select the Convert to Number option.

Convert to Number

  • You will be able to remove the leading zero.

Removing leading zero in Excel


Method 3 – How to Use VALUE Function to Remove Leading Zero?

You will use the VALUE function to remove leading zeros in Excel. This function returns the numeric value of data in a cell.

  • Enter the following formula in cell F5, and AutoFill the formula.
=VALUE(C5)

Output of the VALUE function


How to Hide Zeros in Excel?

Method 1 – Hide zeros from the entire spreadsheet:

Go to File >> Options >> Advanced >> Scroll down to the Display options for this worksheet section: >> Uncheck the Show a zero in cells that have zero value option.

Uncheck the Show a zero in cells that have zero value option

Method 2 – Use a Custom format:

Press Ctrl + 1 >> Format Cells dialog box appears >> Select the Custom option from the Category drop-down list under the Number tab >> Type #;#;;@ in the typing box to hide zeros >> Hit OK.

Using Custom Cells format to hide zeros


What Things You Have To Remember?

  • Leading zeros are crucial for preserving the accuracy and integrity of certain data types, such as identification numbers, product codes, or zip codes.
  • When using the Text to Columns feature, leading zeros may be excluded during the process of splitting the data into separate columns. Be cautious if leading zeros are significant for your data.
  • Leading zeros do not impact calculations in Excel. Excel automatically ignores leading zeros when performing mathematical operations.

Download Practice Workbook


Frequently Asked Questions

1. How do you pad a number with leading zeros?

Answer: To pad a number with leading zeros in Excel, use the TEXT function. For instance, if the number is A5, and you want it to have at least 6 digits, use the formula “=TEXT(A5, “000000”)”. This ensures proper formatting and alignment in your spreadsheet.

2. How do I put 0 in front of a number in Excel to make it a 10-digit number?

Answer: You can put zero(0) in front of a number by formatting the cell in Excel to make it a 10-digit number.

  • Right-click on the cell.
  • Choose Format Cells from the Context menu.
  • Go to the Number tab.
  • Select Custom from Category.
  • Type format “0000000000” to add leading zeros with a 10-digit number.

3. Can leading zeros affect calculations in Excel?

Answer: No, leading zeros do not affect calculations in Excel. Excel automatically treats numbers with leading zeros as numeric values and performs calculations accordingly, ignoring the leading zeros. The leading zeros are purely for visual representation and do not impact the mathematical operations.


Leading Zero in Excel: Knowledge Hub


<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo