To insert a zero before a series of numbers in Excel, you need to use a trick. This is important for telephone numbers and postal codes.
How to insert a zero before a number in Excel
If you want to enter zip codes or telephone numbers in Excel, for example, you must first format the cells so that the zero at the beginning is also displayed.
- To a Zero before a number To insert a cell, first enter the number with the zero. If the zero is not displayed at the beginning, right-click on the cell. A list will appear. Here, select “Custom”. In the “Type” field on the right, enter the following: “0”0. The zero will now appear at the beginning of the entered number.
- Would you like several numbers below each other with a zero at the beginning in your file, you just need to go to the right edge of the already formatted field and drag it down over the next fields. The formatting will now also apply to these cells. If you enter numbers here that start with a zero, it will be displayed.
- For the Enter a five-digit postcode you can also use this trick: Right-click on a cell and select the sub-item “Format cells”. If you click on “Special format” under category, you have the option to select “Postal code”. Now you can enter your postal code with a zero at the beginning and the zero will be displayed.
- If the number is longer than a postal code, as is the case with a Telephone number, cost center or material number If this is the case, there is also this option: Right-click on the cell in question again. This time, however, select the sub-item “Custom” from the menu. Under “Type”, enter as many zeros as your number is long.
- Attention: If your phone number has a hyphen or space, you must take these into account when entering the number of zeros.
- Do you have several numbers listed one below the other in column A and would you like to Pay in the adjacent column display in the same length with zeros at the beginningthis is possible with the help of a formula.
- To do this, click in column B1 next to the number in column A1. Enter the formula =TEXT(A1;”0000000″) next to “fx” at the top. Insert as many zeros into the formula as the longest number in column A. For example, if the number in column A1 only consists of six numbers, it will now be displayed with a zero at the beginning. If there are five numbers, it will be displayed with two zeros at the beginning in this example.
- To complete the series of numbers in column B, all you have to do is click on the bottom right corner of the formatted cell and drag the field down. Now all the numbers from column A will be displayed in column B in the same length with zeros at the beginning.
These articles might also be of interest to you:
- Create an Excel chart: Simply explained in 4 steps
- Insert trend line in Excel – here's how
- Excel AND function: How to use it