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.

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

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.

• You will be able to convert numbers to text to pad 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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

• Do like the screenshot below.

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

• Then name the Measure according to your choice and place the formula in the Formula option.
• Press OK.

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

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.

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

• 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.
  

  Click the image to view it in full size

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

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

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

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

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.

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.

• You will be able to remove the leading zero.

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.

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.

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.

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