How to start numbers with 0 (zero) in Excel?
when we type 0 at the beginning of a number in cell, the 0 will be erased automatically when we press Enter key or move mouse to other cells. Therefore, are there ways to add leading zeros and start numbers with zeros in Excel? Yes! This article introduces 6 solutions for you.
Start numbers with 0 by adding an apostrophe at the beginning of cells
- Method 1: Start a number with 0 by manually adding an apostrophe at the beginning
- Method 2: Start multiple numbers with 0 in bulk by a cool took
Start numbers with 0 by Format Cells feature
- Method 3: Start numbers with a fixed number of 0 by formatting cells
- Method 4: Start numbers with 0 and display all number in fixed length by formatting cells
Start numbers with 0 by formulas
- Method 5: Start numbers with a fixed number of 0 by formula
- Method 6: Start numbers with 0 and display all numbers in fixed length by formula
Method 1: Start a number with 0 by manually adding an apostrophe at the beginning
You can manually type an apostrophe and zeros before a number to start the number with 0. See screenshot:
Note: This method will change the number to text format.
Method 2: Start multiple numbers with 0 in bulk by a cool took
If you need to start multiple numbers with zeros, manually typing apostrophe and zeros will be time-consuming and tedious. This method will recommend the Add Text feature of Kutools for Excel to add apostrophe and zeros at the beginning of each cells with several clicks.
Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 60-day, no credit card required! Get It Now
1. Select the number cells, and click Kutools > Text > Add Text to enable the Add Text feature.
2. In the Add Text dialog, please type ‘000 (you can change the number of zeros as you need) in the Text box, check the Before first character option, and click the Ok button.
Note: This method will change all selected numbers to text format.
Method 3: Start numbers with a fixed number of 0 by formatting cells
If you want to add leading zeros to numbers but remain the number formats, you can apply the Format Cells feature to get it done easily in Excel.
1. Select the numbers you will start with zeros, right click, and select Format Cells from the context menu.
2. In the Format Cells dialog, under Number tab, please click to select Custom in the Category list box, type 0000########## into the Type box, and click the OK button.
Notes:
- In format code 0000##########, 0000 means to display 4 zeros before every selected number.
- 0000########## will display every selected number as a whole number. If you need to remain decimal places, you can change the format code to 0000##########.00
Now you will see every selected number starts with 4 zeros at once. See screenshot:
Method 4: Start numbers with 0 and display all number in fixed length by formatting cells
In this section, I will guide you to format numbers to display in a fixed length with leading zeros in Excel. Please do as follows:
1. Select the numbers you will start with zeros, right click, and select Format Cells from the context menu.
2. In the Format Cells dialog, under Number tab, click to select Custom in the Category list box, type the format code 000000000 into the Type box, and click the OK button.
Notes:
- The format code 000000000 will display every selected number in 9 digits . If original number is less than 9 digits, use zeros as placeholders in the left; if original number is greater than 9 digits, the number displays as before.
- 000000000 will display every selected number as a whole number. If you need to remain decimal places, you can change the format code to 000000000.00.
Now you will see every selected number displays 9 digits with leading zeros. See screenshot:
Method 5: Start numbers with a fixed number of 0 by formula
You can use a formula to add leading zeros to numbers, and force them to start with a fixed number of zeros easily in Excel.
Select a blank cell, enter below formula, and then drag the AutoFill handle down to copy this formula to other range as you need.
=IF(ISNUMBER(B3)=TRUE,"00"&B3,"")
Note: In above formula, B3 is the first number cell you will add leading zeros for.
Method 6: Start numbers with 0 and display all numbers in fixed length by formula
You can also apply formulas to force a number to display in a fixed length with using zeros as placeholder at the left.
Select a blank cell, enter below formula, and then drag the AutoFill handle down to copy this formula to other range as you need.
=TEXT(B3,"000000000")
Note: In above formula,
- B3 is the first number cell you will add leading zeros for.
- 000000000 means to display the number in 9 digits. If the number is less than 9 digits, use zeros as placeholders in the left.
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
