Buzz
In Excel, number sequences are often displayed as data, and when there are leading zeros, they tend to get truncated. To ensure the display of leading zeros, follow the steps provided by Mytour.
As you can see in the image below, two number sequences are displayed—one with leading zeros and one without.
Both sequences are entered as 090412345, but only the lower data cell retains the leading zero, while the upper data cell loses it upon pressing Enter.
Let's explore how to preserve the leading zero in a data cell like the one below.
1. Convert to Text Format
Method 1:
To convert a data cell to Text format, you can click on the cell (or select a range of multiple cells).
Next, open the Home ribbon on the toolbar, click on Number Format in the Number section of this ribbon.
Choose the Text option from the displayed list.
After entering a series of numbers with leading zeros, your zero will no longer be truncated as before. However, you will notice a green triangle in the top corner. This is Excel's way of informing you that the data you are entering is numerical, not text, as previously configured for the parameter cell.
Click on the error notification next to it and select Ignore Error to dismiss this error message.
Rectifying errors can be done in bulk by selecting them all and clicking on the error notification icon, then choosing Ignore Error.
Method 2:
Another way to input data into a cell is by adding a single quote ' before entering numerical data.
As you can see in the image below, if you add a single quote before a sequence of numbers starting with 0, when you press Enter, the leading 0 will not be truncated.
Similar to Method 1, you will also encounter Excel's notification about numerical data instead of text data.
Simply dismiss its error by selecting Ignore Error.
Likewise, you can Ignore Error for a series of data cells.
2. Convert the data cell to Zip Code format
There's another method to display leading zeros in Excel, and that is by converting the data cell to Zip Code format.
To achieve this, right-click on the data cell you want to convert, then choose Format Cells from the displayed list.
When the Format Cells function window appears, click on the Number tab.
Next, navigate to the Special section under the Category in the dialog box.
Select Zip Code under the Type section.
As you'll notice, your data cell still retains the leading zero at the beginning of the number sequence.
Moreover, because of the Zip Code format, your data cell can perform calculations just like regular cells, not limited to being a display-only Text format.
Thank you for reading and following the article on How to Display Leading Zeros in Excel by Mytour. That concludes our tricks here; see you in other instructional posts.
