How to Get Rid of Leading Zeros in Excel
How to enter number starting with zero in Excel
Having trouble with entering values that start with zero? By default, Excel removes any leading zeros in front of a number when you type in such a value and press enter. So, this behavior makes it a bit hard to enter some phone numbers or ZIP codes, like those starting with a zero. In this guide, we're going to show you how to enter number starting with zero in Excel.
Download Workbook
You have 3 options for enter number starting with zero in Excel:
- Using an apostrophe (')
- Using either Text or Special number formats
- Using a custom number format
Let's see each method in more detail.
Apostrophe (')
Excel evaluates any value that starts with an apostrophe as text, and text values can start with zero (0). That's it! Excel will not show the leading apostrophe character in the beginning when displaying the cell value. You can still see and adjust it when editing the cell value.
Instead of entering an extra character every time, the downside of this method is the fact that you will see an erroneous error indicator for that cell. To get rid of this warning, click the icon on the right-side of the cell, and select Ignore Error item in the list. Please note that choosing Convert to Number here will remove any leading zeros.
Another note regarding this method is that if you want to enter a text that starts with an apostrophe, you may want to consider beginning the text with double apostrophe characters ('').
Text and Special Number Formats
Alternatively, you can use Excel's built-in format options like Text and Special number formats. Number formats alter the display of the values without changing the actual value.
Text number format essentially tells Excel to accept everything inside the cell as a part of a text sting. As a result, Excel doesn't care whether your number starts with a zero or not.
- To change the number formatting of a cell, first, select the cell or cells you want to update.
- Next, either press the Ctrl + 1 combination or use the right-click (context) menu to open the Format Cells dialog
- Activate the Number tab if it isn't
- Select Text in the right panel
- Click OK to apply
You can now enter any value without "auto-correct".
If you want to enter values in a more specialized format, check out the options under the Special category. You can select between 4 pre-defined options:
- Zip Code
- Zip Code + 4
- Phone Number
- Social Security Number
The difference between Text and Special number formats is that the Special formats have a fixed structure and number of digits they will accept. This means that you do not need to enter a leading zero because Excel will fill it based on number format. For example, if you select ZIP Code number format and type 123, Excel displays the value as 00123.
Note that in this method, Excel is not actually adding zeros, it;s just altering how you see the value.
Custom Number Format to enter number starting with zero
The final approach is the advanced version of number format method. Excel allows you to code your custom number formatt. So, if the existing formats do not fit your needs, try to creating your own.
To code a custom formatting, open the Format Cell dialog again, and select Custom from the left pane. You need to enter the format code in the Type box and click OK.
Here is a cheat sheet for custom formatting codes:
- 0 represents digits can be filled by a leading zero if there isn't a number. For example, if you want to a 3-digit number which should have leading zeros, you should use 000.
- If you enter 1, you will see 001.
- 12 will be 012.
- 123 will be 123.
- # represents digits can be filled by a leading zero. For example, if your number format is 0##, leading zero will be added only one time.
- 1 will be 01
- 12 will 012
- 123 will be 123
One more example: Excel's Special – ZIP number format uses 00000. You can learn more about custom number formats in Number Formatting in Excel – All You Need to Know.
How to Get Rid of Leading Zeros in Excel
Source: https://www.spreadsheetweb.com/enter-number-starting-with-zero-in-excel/