Wasim Akram
If you are working with Microsoft Excel, you might find that leading zeros before numbers are removed automatically. Excel’s default options remove the leading zeros from numbers. In this article, I am going to show you how to add leading zeros to make 10 digits in excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
10 Suitable Ways to Add Leading Zeros to Make 10 Digits in Excel
In the following, I have described 10 simple and suitable ways to add leading zeros to make 10 digits in excel.
Suppose, we have a dataset of some Employee Name and their Contact Number. Now, I will add leading zeros before the numbers to make 10 digits.
1. Use Format Cells to Add Leading Zeros in Excel to Make 10 Digits
However, If you are looking for a simple way to add leading zeros and make 10 digits in excel, then you are at the right place. In this method, I will show you how I made 10 digits using the format cells feature of excel.
Steps:
- Firstly, select the contact numbers placed in cells (C5:C11).
- Later, press Ctrl+1 to open the “Format Cells” window.
- Secondly, in the format cells window hit the “Custom” button and put “0000000000” in the type section.
- Thereafter, hit OK to continue.
- As a result, we have our 10-digit output adding leading zeros before the numbers.
2. Apply Text Format to Insert Leading Zeros to Make 10 Digits
Although, you can also change the cell format to text format and manually put zeros before the numbers to reach to your destination.
Steps:
- First, choose a list of numbers from the table. Here I have chosen cells (C5:C11).
- At the same time change the format to the “Text” format from the home ribbon.
- Afterward, manually put zeros before the numbers.
- Don’t worry. The leading zeros won’t go away as we converted those selected cells to “Text” format.
- As soon as you fill those cells an “Error” sign will appear in a corner.
- But you can remove them by clicking the “Error” icon and pressing “Ignore Error”.
- Here, we have successfully got 10-digit numbers in all cells by adding leading zeros.
Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)
3. Perform TEXT Function to Add Leading Zeros to Construct 10 Digits
In spite of using the text format feature, you can apply the TEXT function in excel to add leading zeros to make 10 digits.
Steps:
- Select a cell to write the formula. Here I have selected cell (E5).
- Apply the formula-
=TEXT(C5,"0000000000")Where,
- The TEXT function converts the number into text format within a string.
- Hereafter, press the Enter
- Next, drag the “fill handle” to fill all the cells.
- In conclusion, you will get your desired output in a new column with 10 digits adding zeros before the numbers.
4. Add Apostrophe Sign Before Numbers to Add Leading Zeros in Excel
Specifically, you can add an apostrophe sign (‘) before the numbers to keep leading zeros in excel. Follow the steps below-
Steps:
- First of all, select a cell (C5) and add an apostrophe sign (‘) before the number adding zeros.
- Meanwhile, you will see the output with zeros in front of the cell.
- Hence, do this process for all the cells in the table.
- Even though the leading zeros will be added to the table but you will find an “Error” sign with all the numbers.
- For this reason, choose all the cells with error.
- Therefore, click the “Error” icon, and from the drop-down list press “Ignore Error”.
- Eventually, we reached our destination adding leading zeros to construct numbers to 10 digits.
5. Utilize RIGHT Function to Cast Leading Zeros to Make 10 Digits
Differing from these manual methods use the RIGHT function to cast leading zeros to construct 10 digits.
Steps:
- Here select a cell (E5) to apply the formula.
- Now, put the formula down-
=RIGHT("0000000000"&C5,10)
- Afterward, hit the Enter button to continue.
- Hence, pull the “fill handle” down.
- Finally, you will get the precious result by adding leading zeros to make 10 digits numbers in excel.
6. Add Leading Zeros to Make 10 Digits with Excel BASE Function
You can also utilize the BASE function equally to add leading zeros before all the numeric values in the cell.
Steps:
- Yet we will select a cell (E5) to write the formula.
- Apply the formula-
=BASE(C5,10,10)Where,
- The BASE function returns a numeric value into text format.
- In the same fashion, click Enter to complete the formula and get the output for the applied formula.
- Alongside, drag the “fill handle” down to fill.
- Specifically, in the final output column, we will get the finished product.
7. Use Power Query’s PadText Function to Include Leading Zeros
Power Query is a tool to analyze and organize data which is mainly used for data analysis. With this feature of Excel, you can import data from various sources and shape and convert it according to your choice. In this method, I am explaining how to add leading zeros in excel to make 10 digits using the power query’s PadText function.
Suppose, you have a list of numbers saved on your PC. Now, we will import the data in excel using the “Power Query” tool and then apply the PadText function to construct to 10 digits.
Steps:
- In the first step, open your workbook and go to Data > Get Data > From File > From Text/CSV.
- Eventually, a new window will pop up named “Import Data”.
- Once clicking the file click “Import”.
- Consequently, the data will be imported to your excel worksheet.
- Then click “Transform data”.
- Later the “Power Query Editor” will open.
- At first press the “Custom Column” option from the “Add Column”.
- Therefore, a new window will pop up named “Custom Column”.
- From the new window, name the column name of your choice and apply the following formula-
=Text.PadStart([Column1],10,"0")- Press OK to continue.
- On the contrary, our contact number list is ready with leading zeros.
- Now to get them in our excel worksheet click the “File” option.
- Below choose “Close & Load” to get the final output.
- Thus our final result is ready with 10 digits adding zeros in front of numbers in a new worksheet.
8. Combine REPT and LEN Functions to Join Leading Zeros in Excel
In Microsoft Excel using functions, you can complete any task you want. With the combination of the REPT and LEN functions, you can attach leading zeros just before numeric values and make 10 digits in excel.
Steps:
- For applying the formula select a cell (E5).
- Write the following formula down-
=REPT(0,10-LEN(C5))&C5Where,
- The REPT function repeat characters a defined number of times.
- The LEN function demonstrates the length of a text string as the number of characters.
- Hence, click Enter.
- Afterward, pull the “fill handle” down to fill the column.
- Finally, using functions we got our 10-digit number by adding zeros before the numbers.
9. Excel VBA to Adjoin Leading Zeros
Fortunately, you can try the VBA code from the following to adjoin leading zeros before the numbers.
Steps:
- Presently, choose cells (C5:C11) and press Alt+F11 to open the “Microsoft Visual Basic for Applications” window.
- Therefore, in the new window open a “Module” from the “Insert” option.
- Put the following code and press “Run” to apply the code to those chosen cells–
Sub AddLeadingZeroes()
Range("C5", Range("C5").End(xlDown)).Select
Selection.NumberFormat = "0000000000"
End Sub
- Thus the cells will be added zeros before the numbers making it 10 digits.
10. Apply DAX Formula to Attach Leading Zeros
If you want you can apply the DAX formula to attach leading zeros before numbers in excel. In this method, I have shared steps to add zeros before numbers to make 10 digits in excel.
Steps:
- Here select the whale dataset and click “Pivot Table” from the “Insert” option.
- Inside the worksheet choose a cell where you want to create the pivot table choosing “Existing Worksheet”.
- Now, press OK to continue.
- As soon as clicking OK a right pane will appear named “PivotTable Fields”.
- Hence, place the cursor over the “Range” menu and right-click the mouse button to get options.
- Hereafter, press “Add Measure”.
- Then name the list according to your choice and place the formula in the “Formula” section-
=CONCATENATEX(Range,FORMAT([Contact Number],"0000000000"),",")- Accordingly, hit the OK button to continue.
- In conclusion, you will get the desired result in the selected cell.
Read More: How to Add Leading Zeros in Excel by CONCATENATE Operation
Things to Remember
- Suppose you have numbers in your workbook with the same amount of numeric values in every cell. In that case, you can add a fixed number of leading zeros before the numbers using the CONCATENATE function. To learn more, follow this article.
Conclusion
In this article, I have tried to cover all the effective methods to add leading zeros to make 10 digits in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.
