The dataset contains Employee Names and their Contact Number. To add leading zeros before the numbers to make 10 digits:
Method 1 – Using Format Cells to Add Leading Zeros
Steps:
- Select the contact numbers (C5:C11).
- Press Ctrl+1 to open the “Format Cells” window.
- Select “Custom” and enter “0000000000” in Type.
- Click OK.
- The 10-digit output with leading zeros before the numbers will be displayed.
Method 2 – Applying Text Format to Insert Leading Zeros
Change the cell format to text format and manually enter zeros before the numbers.
Steps:
- Select a list of numbers (C5:C11).
- Change the format to the “Text” in Home.
- Enter zeros before the numbers.
- An “Error” sign will be displayed.
- Remove it by clicking the “Error” icon and choosing “Ignore Error”.
- This is the output.
Read More: How to Add Leading Zeros in Excel Text Format
Method 3 – Using the TEXT Function to Add Leading Zeros
Use the TEXT function.
Steps:
- Select a cell to enter the formula. Here, E5.
=TEXT(C5,"0000000000")
The TEXT function converts the number into text format within a string.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Method 4 – Adding an Apostrophe Before the Numbers to Add Leading Zeros
Steps:
- Select C5 and add an apostrophe (‘) before the number adding zeros.
- See the output with zeros.
- Repeat this process for all the cells in the table.
- An “Error” will be displayed.
- Select all the cells with errors.
- Select the “Error” icon and click “Ignore Error”.
- This is the output.
Method 5 – Utilizing the RIGHT Function to enter Leading Zeros
Use the RIGHT function.
Steps:
- Select a E5 and enter the formula.
=RIGHT("0000000000"&C5,10)
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Method 6 – Adding Leading Zeros with the Excel BASE Function
Steps:
- Select E5 and enter the formula.
=BASE(C5,10,10)The BASE function returns a numeric value in text format.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Read More: How to Put 0 in Excel in Front of Numbers
Method 7 – Using the Power Query PadText Function to Include Leading Zeros
You have a list of numbers. Import the data using the power query tool and apply the PadText function.
Steps:
- Open your workbook and go to Data > Get Data > From File > From Text/CSV.
- In “Import Data”, select the file.
- Click “Import”.
- Data will be imported into your Excel worksheet.
- Click “Transform data”.
- In the “Power Query Editor”, select “Add Column”.
- Choose “Custom Column”.
- In the new window, name the column and use the following formula:
=Text.PadStart([Column1],10,"0")- Click OK.
- The contact number list is displayed with leading zeros.
- Click“File”.
- Choose “Close & Load”.
- This is the output.
Method 8 – Combining the REPT and the LEN Functions to add Leading Zeros
Use the REPT and the LEN functions.
Steps:
- Select E5 and enter the following formula:
=REPT(0,10-LEN(C5))&C5The REPT function repeats characters a defined number of times.
The LEN function returns the length of a text string.
- Press Enter.
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Method 9 Using Excel VBA to Add Leading Zeros
Steps:
- Select C5:C11 and press Alt+F11 to open the “Microsoft Visual Basic for Applications” window.
- Open a “Module” in “Insert”.
- Enter the following code and click “Run” .
Sub AddLeadingZeroes()
Range("C5", Range("C5").End(xlDown)).Select
Selection.NumberFormat = "0000000000"
End Sub
- This is the output.
Method 10 – Applying the DAX Formula to Add Leading Zeros
Steps:
- Select the whole dataset and click “Pivot Table” in “Insert”.
- Choose a cell to create the pivot table in “Existing Worksheet”.
- Click OK.
- In “PivotTable Fields”, place the cursor over “Range” and right-click.
- Click “Add Measure”.
- Name the list and enter the formula in “Formula”.
=CONCATENATEX(Range,FORMAT([Contact Number],"0000000000"),",")- Click OK.
- This is the output.
Things to Remember
- You can add a fixed number of leading zeros before the numbers using the CONCATENATE function.
Download Practice Workbook
Download the practice workbook.
Related Articles
<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
