How to Add Leading Zeros in Excel?

Add Leading Zeros in Excel:

Excel permanently removes the Leading Zeros from any numbers automatically. To overcome this problem, you need to know how to Add Leading Zeros in Excel 365. In this article, you will learn some quick and straightforward ways to Add Leading Zeros in Excel. Let’s step into this article and know the methods.

Change Format as Text:

• You can change the format of your data into Text. So that Excel will treat any data you enter as Text data.
• If you enter the numbers, it will take it as a text by enabling this option.
• To Change the format from general to Text, you need to follow the below steps.

1. Select the cells you want to give input with leading zeros.
2. On the Home tab, in the Number Section, Click the Format Drop-down Option.
3. Select Text from the menu.

Now, you can enter the leading zeros in your data, and it will not disappear because you changed the format as Text. So it will take the leading zeros as text values.

Add Custom Format:

• You can add a Custom *Format * to format numbers with leading zeros.
• To add a Custom Format, Select the ** range of cells** that you want to add leading zeros.
• You have to open the Format Cells dialog box , Right-Click on anywhere in the spreadsheet and click Format cell, or press CTRL + 1 keyboard shortcut to open the dialog box.
• On the Number ** tab, Select **Custom Category from the menu.
• Now, you need to add a Custom Format in the Type option. If you want to add 6-digit numbers, including leading zeros, give six zeros in the input box.
• Hit the ** Ok** button.

• Now, you can see the selected cells with leading zeros. If you want to copy and paste this values, leading zeros will disappear.

Leading Apostrophe:

• You can Add Leading Zeros with the help of (Apostrophe) ‘ added in front of your data.
• Excel will consider the data after the apostrophe is text data, not numbers.
• When you click Enter , the added leading zeros will stay visible at the cells. But ‘ will not be visible in the spreadsheet. It is visible only in the Formula bar when the cells are selected.

Text Function:

• You can Add Leading Zeros using Text Function in Excel.
• To do this, first, you need to enter the data in the spreadsheet.
• Then, you have to apply the Text Function formula to add leading zeros.
• ” =TEXT( Value, Format ) “uses this function in the formula bar.
• Value – This is the value you want to convert as a text.
• *Format * – Define how many digits you want to use, and add zero for each number.

• In the above example, you can see the Format of Text Function, “=TEXT(C4,”000000″)” and it will add leading zeros in the Text Function column.

Right Function:

• You can also use this Right Function to Add Leading Zeros to your data.
• Right-Function will get the rightmost text characters from your data.
• ” =RIGHT( Text, [Number] )” this is the formula you need to enter in the Formula bar.
• Let’s see the example.

• The above image has shown how to Add Leading Zeros with the Right Function.

Base Function:

• You can also use the Base Function to convert the data as text data.
• “=BASE ( Number, Base, [Min Length] )” this the Base Function formula.
• Number – In this section, you must give the input data you want to convert as text.
• Base – This is the base you want to convert the data to.
• Min Length (optional) – This is the minimum length of your text data.

• In the above image, you can see the Base Function structure in the formula bar. Apply this function to convert the data as Text.

Power Query and Text.Pad Start Function:

• Generally, Power Query is used for data transformation, So it’s one of the solutions for Converting data into Text.
• To do this, you need to select the data and Open the Power Query Editor Window.
• After inserting your data into Power Query, you need to add another column using this formula ” = Text.PadStart ( [Number], 6, “0”) “
• Then, On the Add Column tab, Select the Custom Column option.
• You have to create the new name for the custom column, add the above formula on the Custom Column Formula box.
• Hit the Ok button.

• It will create a new column with data. All data is added with leading zeros.

Bottom Line:

In the above article, you can understand the Steps to Add Leading Zeros in Excel 365 with practical examples. Share your suggestions/queries in the below comment section. Thanks for visiting Geek Excel. Keep Learning!!