How to Pad Zeros in Excel? (Complete Guideline)

In this Excel tutorial, you will learn how to pad zeros in Excel. We will use different Excel functions (TEXT, TEXTJOIN, CONCATENATE, REPT, BASE, and so on), built-in features, Power Query, Pivot table, etc. to pad zeros in Excel. From this article, we will also learn how to retain leading zeros in a CSV file and display missing leading zeros in front of numbers.

While preparing this article, we used Microsoft Office 365 for applying all operations but they are also applicable in Office 2010 and later versions.

Excel’s default behavior is to remove leading zeros in numeric cells by default, causing data inconsistencies. Formatting as text or using an apostrophe preserves them, ensuring data integrity. Adding leading zeros is vital for identification numbers, products, and zip codes.

Download Practice Workbook

Why Is it Important to Pad Zeroes in Excel?

Padding zeroes in Excel is crucial to maintain data accuracy and consistency, especially for identification numbers, codes, or zip codes. It ensures data integrity, prevents misinterpretation, and enables proper sorting, filtering, and calculations.

Without leading zeroes, Excel may mishandle or lose essential information, impacting data quality. Padding ensures data remains in the correct format, aids in database integration, and facilitates error-free data transfer, making it essential for accurate and reliable data handling in various contexts.

How Many Ways to Pad Leading Zeros in Excel?

We have 7 major ways to help us pad zeros in Excel:

• Using Custom Cell formatting.
• Inserting symbols.
• Using various built-in Excel Functions.
• Making use of Power Query and much more.

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

You can use the Custom format from the Format Cells dialog box to pad leading zeros 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.

• Finally, you will be able to format a number as a phone number keeping the leading zeros.

2. How to Convert Number to Text to Pad Zeros in Excel?

We can convert the numbers to text to pad zeros in Excel. We will get this option in the Format Cells dialog box.

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

• As a result, you will be able to convert numbers to text to pad zeros.

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 with keeping the leading zeros.

Here, we inserted numbers with the leading apostrophe in the Phone Number column to get number data.

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

We will use the Ampersand (&) symbol to pad zeros with a given number or text and results in text format.

• Write down the following formula in cell F5 to show leading zeros. Hence, AutoFill the formula to the rest of the cells in column F.

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

We’ll use Excel’s built-in functions such as TEXT, TEXTJOIN, REPT, RIGHT, CONCATENATE, LEN, and BASE functions to pad zeros in Excel.

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

You can 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 as you want.

• Insert the following formula in cell F5, and AutoFill the formula.

Note:
You 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 that you can apply to insert leading zeros in Excel is the BASE function.

• Type the following formula in cell F5, and AutoFill it.

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

To add leading zeros in Excel, you can apply the DAX formula. DAX formula is available in the Pivot table.

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

• After that, do like the screenshot below.

• Now, 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.
• Hence, press OK.

• Finally, you will be able to add leading zeros to your selected cells.

7. How to Use Power Query to Pad Zeros in Excel?

You can use the Power Query Editor to pad zeros in Excel. Applying the Power Query is one of the most efficient ways to keep the leading zeros in an Excel CSV file (Note: The CSV file has leading zeros, but does not show during import).

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

• Now, import your CSV After importing your CSV file, you will see a preview of your data like the following picture.
• After that, click on Transform Data.

• Hence, a Power Query Editor window will be open on your worksheet as shown in the image given below.
• Now, click on the marked area in the ID column >> Choose the Text option.
  

  Click the image to view it in full size

• After that, a Change Column Type dialog box will open, and choose the Replace Current option.
  

• As a result, you will be able to add zeros in the ID column as shown in the following image.

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

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

How Many Ways to Remove Leading Zero in Excel?

We have three ways to remove the leading zeros in Excel using the built-in features and functions. Let’s follow the instructions below to learn!

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.

Here, 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

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.

• As a result, you will be able to remove the leading zero.

3. How to Use VALUE Function to Remove Leading Zero?

In this section, 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?

You can hide zeros from the entire sheet in two ways.

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.

2. Use a Custom format: To do that, 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.

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.

Conclusion

From the above discussions, we have learned how to pad zeros in Excel. We also learned how to remove or hide leading zeros using Excel’s built-in functions and features.
Leading zeros are important for presenting text formatted numbers, product codes, phone numbers or zip codes. To maintain leading zeros in Excel for accurate data handling, you can use any of the methods explained in this article.

Leading Zero in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel