This tutorial shows different ways to add leading zeros in Excel: how to keep zeros as you type, show leading zeros in cells, remove or hide zeros. If you use Excel not only to calculate numbers, but also maintain records such as zip codes, security numbers or employee ids, you may need to keep leading zeros in cells. However if you try typing a zip code like '00123' in a cell, Excel will immediately truncate it to '123'. The point is that Microsoft Excel treats postal codes, phone numbers and other similar entries as numbers, applies the General or Number format to them, and automatically removes preceding zeroes. Luckily, Excel also provides the means to keep leading zeros in cells, and further on in this tutorial you will find a handful of ways to do it. • • • • • • • How to keep leading zeros in Excel as you type For starters, let's see how you can put 0 in front of a number in Excel, for example type 01 in a cell. For this, simply change the cell format to Text: • Select the cell(s) where you want to prefix numbers with 0. • Go to the Home tab > Number group, and select Text in the Number Format box. The leading zero before a decimal point assists users in recognizing decimal numbers less than one. Without the leading zero a cursory look at a series of digits. • As soon as you type a zero(s) before number, Excel will display a small green triangle in the top-left corner of the cell indicating that something is wrong with the cell contents. To remove that triangle, select the cell(s), click the warning sign, and then click Ignore Error. The following screenshot shows the result: Another way to keep leading zeros in Excel is to prefix a number with an apostrophe ('). For example, instead of typing 01, type '01. In this case, you don't need to change the cell's format. Bottom line: This simple technique has a significant limitation - the resulting value is a text string, not number, and consequently it cannot be used in calculations and numeric formulas. If that is not what you want, then change only the visual representation of the value by applying a custom number format as demonstrated in the next example. How to show leading zeroes in Excel using a custom number format To display leading zeroes, apply a custom number format by performing these steps: • Select a cell(s) where you want to show leading zeros, and press Ctrl+1 to open the Format Cells dialog. • Under Category, select Custom. • Type a format code in the Type box. In most cases, you will need a format code consisting of 0 placeholders, like 00. The number of zeros in the format code corresponds to the total number of digits you want to show in a cell (you will find a few examples below). • Click OK to save the changes. For example, to add leading zeros to create a 5-digit number, use the following format code: 00000 By using Excel custom numbers formats, you can add leading zeros to create fixed-length numbers, like in the above example, and variable-length numbers. It all boils down to which placeholder you use in the format code: • 0 - displays extra zeros • # - does not display extra zeros For example, if you apply the 000# format to some cell, any number you type in that cell will have up to 3 leading zeros. Your custom number formats can also include spaces, hyphens, parentheses, etc. The detailed explanation can be found here:. The following spreadsheet gives a few more examples of custom formats that can show leading zeros in Excel. A B C 1 Custom format Typed number Displayed number 2 00000 123 00123 3 000# 123 0123 4 00-00 1 00-01 5 00-# 1 00-1 6 000-0000 123456 012-3456 7 ###-#### 1234 And the following format codes can be used to display numbers in special formats such us zip codes, phone numbers, credit card numbers, and social security numbers. A B C D 1 Custom format Typed number Displayed number 2 Zip code 01234 3 Social security 0 12345678 0 4 Credit card 0000-0000-0000-0067-3455-5678-9123 5 Phone numbers 00-0-000-000-006789 00-1-234-555-6789. Excel has a few predefined Special formats for postal codes, telephone numbers and social security numbers, as shown in the screenshot below: Bottom line: This method is best to be used in situations when you work with a numeric dataset and the results should be numbers, not text.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2019
Categories |