Leading zeros in Excel can be a huge help in many different projects. However, they’re not always necessary - sometimes you want them, and sometimes you don’t. In this article, you can learn how to control leading zeros in your spreadsheets and switch between the different modes depending on your current work.
A leading zero is any “0” digit before the first nonzero digit in a number string. For example, the string "001" has two leading zeros. This formatting in Excel can be useful when working with a huge data set, making organization and sorting much easier.
If you don’t want to manually type each leading zero, there are plenty of methods to add them automatically with Excel.
Table of Contents
- How to add leading zeros in Excel
- How to remove leading zeros in Excel
- Watch: [Easy Way] How to Remove Leading Zeros in Excel-2023
- Final thoughts
How to add leading zeros in Excel
There are different ways to add leading zeros in Excel. Let’s explore the possibilities that fit your needs.
Method 1. Use the Format Cells menu
-
Select all the cells you want to edit and add leading zeros to. You can do this by dragging your cursor, or holding the Shift key on your keyboard while clicking with your mouse.
-
Right-click on your selection, then choose Format Cells. Alternatively, use the Ctrl + 1 keyboard shortcut to open the window quicker.
-
Select Custom from the Category section.
-
Type any number of 0’s in the Type input field. In our example, we used a 6 digit string, meaning we’ll type in “000000”.
- Click the OK button. All of the numbers in your selection should have the corresponding amount of leading zeros now. Please note that this method does not change the value of your cells! The leading zeros are just for appearance.
Method 2. Use the TEXT function
If you’re working with text and not number values, you can add leading zeros using the TEXT function.
To add leading zeros using the TEXT formula, you'll want to use the following:
=TEXT(cell, "number of zeros") for example =TEXT(A1, "000000")
This is going to create a fixed length, in our case 6-character text string. Take note that this method makes it so you’re unable to use your values for calculations and other formulas.
Method 3. Use the REPT and LEN functions
A different approach to adding leading zeros to a text string is by utilizing the REPT and LEN functions together. Again, by following this method you won’t be able to continue using functions and calculations.
The formula is as follows:
=REPT(0, number of zeros-LEN (cell)) &cell for example =REPT(0, 6-LEN (A1)) &A1
Doing this, Excel will automatically add a number of leading zeros to the value in your A1 cell until it creates a 6 character long string.
How to remove leading zeros in Excel
If you want to revert back to your normal numeric values, don’t worry — it’s possible. There are a few methods to use that might fit your approach better.
Method 1. Change the file format
- Select all the cells you want to revert and remove leading zeros from.
- Switch to the Home tab using the Ribbon interface found on top of your window.
- In the Number section, you’ll see a drop-down menu displaying Special. Click on this menu to open the other options.
- Select General. Doing so will revert your cells to the default formatting.
Method 2. Convert text to number
If you changed your numeric values to text in order to add leading zeros, you might want to convert it back to numbers and lose the 0’s. The easiest way of doing this is by selecting the cell you want to convert, then clicking on the yellow exclamation mark. (Bottom-right corner of the cell.)
From the drop-down menu, select Convert to Number. You’ll immediately see it change back to its original numeric value.
Watch: [Easy Way] How to Remove Leading Zeros in Excel-2023
Final thoughts
If you need further help with Excel and other Office applications, don’t hesitate to contact our customer service team, which is available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern day technology!
Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.