How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)

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.

Add Leading Zeros 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.

Use Format Cells to Add Leading Zeros in Excel to Make 10 Digits

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

Use Format Cells to Add Leading Zeros in Excel to Make 10 Digits


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.

Apply Text Format to Insert Leading Zeros to Make 10 Digits

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

Apply Text Format to Insert Leading Zeros to Make 10 Digits

  • Here, we have successfully got 10-digit numbers in all cells by adding leading zeros.

Apply Text Format to Insert Leading Zeros to Make 10 Digits

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.

Perform TEXT Function to Add Leading Zeros to Construct 10 Digits

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

Perform TEXT Function to Add Leading Zeros to Construct 10 Digits


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.

Add Apostrophe Sign Before Numbers to Add Leading Zeros in Excel

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

Add Apostrophe Sign Before Numbers to Add Leading Zeros in Excel

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

Add Apostrophe Sign Before Numbers to Add Leading Zeros in Excel


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)

Utilize RIGHT Function to Add Leading Zeros to Make 10 Digits

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

Utilize RIGHT Function to Add Leading Zeros to Make 10 Digits


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.

Add Leading Zeros to Make 10 Digits with Excel BASE Function

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

Add Leading Zeros to Make 10 Digits with Excel BASE Function


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.

Use Power Query’s PadText Function to Add Leading Zeros in Excel to Make 10 Digits

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

Use Power Query’s PadText Function to Add Leading Zeros in Excel to Make 10 Digits

  • Later the “Power Query Editor” will open.
  • At first press the “Custom Column” option from the “Add Column”.

Use Power Query’s PadText Function to Add Leading Zeros in Excel to Make 10 Digits

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

Use Power Query’s PadText Function to Add Leading Zeros in Excel to Make 10 Digits

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

Use Power Query’s PadText Function to Add Leading Zeros in Excel to Make 10 Digits

  • Thus our final result is ready with 10 digits adding zeros in front of numbers in a new worksheet.

Use Power Query’s PadText Function to Add Leading Zeros in Excel to Make 10 Digits


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))&C5

Where,

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

Combine REPT and LEN Functions to Add Leading Zeros in Excel

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

Combine REPT and LEN Functions to Add Leading Zeros in Excel


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.

Excel VBA to Add Leading Zeros to Make 10 Digits

  • 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

Excel VBA to Add Leading Zeros to Make 10 Digits

  • Thus the cells will be added zeros before the numbers making it 10 digits.

Excel VBA to Add Leading Zeros to Make 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.

Apply DAX Formula to Add Leading Zeros in Excel to Make 10 Digits

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

Apply DAX Formula to Add Leading Zeros in Excel to Make 10 Digits

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

Apply DAX Formula to Add Leading Zeros in Excel to Make 10 Digits

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.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo