Sometimes you need to enter values starting from zeros, i.e., 000123 in Excel. And when you type this value, you will see Excel by default removes the leading zeros from the values you have entered, i.e. 000123 to 123. Though it could be something that can annoy someone easily.
The leading zeros may appear in zip codes, mobile numbers, employee ids, or security numbers. For that reason, you will have to keep leading zeros in Excel data. Excel coding takes the postal codes, phone numbers, and other similar values as integers only. That’s why people often face such problems while entering their data according to their needs.
If you want to keep leading zeros in Excel you must be looking for the best ways to sort out this issue. It is possible by following some useful methods.
The Apostrophe Method
The first method simply needs you to put an apostrophe in the cell before the number containing leading zeros. When you place an apostrophe before that number, you are informing Excel that does not follow cell formatting and show the numbers as they are.
It clearly shows that if you want to change the cell formatting to General and want to edit the cell, you will notice no change in it instead it will appear as auto-formatted by Excel.
Below are some steps to be followed:
- Place an apostrophe before the number in the cell, i.e. ‘000123 will display as 000123.
- Now, add the number and press ENTER key.
- The numbers will appear and import smoothly.
How to Keep Leading Zeros in Excel as You Type
The above-mentioned method is undoubtedly a superb way to help keep leading zeros. However, users always tend to find plenty of options so that they can enjoy performing their tasks.
Let’s dig into this method:
- Suppose you enter 01 in a cell and you can simply change the cell format to Text.
- Choose the cells in which you need to keep the values with leading zeros.
- Open the Home tab and then click on the Number option. Choose the Text given in the Number Format box.
- Once the leading zero is placed, Excel by default shows a small green triangle in the upper left side of the cell. It shows that the value is not correct in the cell. Choose the cell and click the warning sign. Press Ignore Error button and it will remove the error indicator.
- Below you can see the outcome.
Custom Format
Other than the above methods, we have a custom formatted method in which you can apply custom formatting to keep the prefix 0. The format will apply only with leading zeros and the rest of the data will not change.
To apply a custom format, you need to follow the steps given below:
- Choose the cell range that is needed to keep leading zeros. Open the Format Cells menu.
- Right-click and choose Format Cells.
- Press the + keyboard shortcut.
- Click on the Number tab and choose Custom from the category menu.
- Add a new custom format in the Type field box. You will have to add the same amount of zeros as the total number of digits you need to add.
- Press OK.
Once the custom format is applied, you will see the data is not changed. When you choose a cell will formatting, you will see the original value in the formula bar. It means if you copy/paste the values, the leading zeros will be removed.
How to Keep Leading Zeros in Excel with Built-in Special Formats
In the Format Cells menu, you can choose the Special option to keep the leading zeros. Then choose the Zip Code option and press OK.
Here you will notice other formats such as Phone Number, Serial Security Number, etc. all these options are by default set on the USA location that can be changed, however.
Eventually, the outcome will appear with leading zeros in your selected cells.
Add Leading Zeros with the RIGHT Function
With the RIGHT function, you can keep the leading zeros in front of the numbers in the cells.
RIGHT(“0000” & cell, string_length)
Here:
“0000” shows the max number of zeros you can add. For instance, when you need two zeros before the number you will type “00”.
Cell is a reference to that cell having the original value.
String_length shows how many characters the resulting string should have.
For instance, you can enter 6 zeros before the value in cell A2, using this formula:
=RIGHT(“000000”&A2, 6)
The formula functions as adding 6 zeros before the value in A2 (“000000”&A2). In the end, you will see the right number of zeros added here:
The max number of zeros is similar to the total string length (6 characters), that’s why each string is 6 characters long. According to your needs, a different number of zeros and total characters can be applied. For instance:
=RIGHT(“00”&A2, 6)
Final Thoughts
So, this is how you can keep leading zeros in Excel while ignoring the preset options of Excel. Keep trying these methods and continue exploring Excel.
