Buzz
When presenting numerical data in Excel for various purposes, sometimes you need to add leading zeros to numbers. Check out this guide by SoftwareTricks.com as we show you how to add zeros to the beginning of a number sequence.
Cells in Excel are classified into different data types. For instance, cells containing a sequence of numbers will automatically convert to the number data type when you finish entering the cell.
When entering a sequence of numbers with a leading zero, Excel will automatically condense it to the most precise number, meaning it removes the leading zero.
Hence, adding a leading zero to the number sequence can be done in various ways. However, it's crucial to note whether you want to maintain the numeric format for that parameter cell or not. Otherwise, it might be converted into a text data cell, and Excel won't automatically trim your number sequence anymore.
1. Adding a zero at the beginning of the number sequence with the result cell as text data.
To convert the result cell into a text data type, you can follow these approaches:
Approach 1: Convert the result cell type before entering the number sequence.
To convert the parameter cell type to text data cell, you can click on the data cell or select a data array.
Next, open the Home ribbon on the toolbar, then click on Number Format under the Number section.
Then, select Text from the displayed list.
Now, enter the number sequence with leading zeros into the converted data cell. You'll notice that your leading zero is no longer omitted as before, but you'll see a green triangle indicator at the corner of Excel.
This is Excel's reminder when it detects that the data you entered is a continuous number sequence. It asks if you've mistakenly filled the cell and whether you want to revert the current data cell back to a number data cell.
Of course, intentionally leaving it as is, you need to click on the triangle and select Ignore Error to bypass the error that Excel prompts, and the triangle will disappear.
Approach 2: Format the parameter cell while entering the number sequence.
There's no need to select the parameter cell and convert it before entering the number sequence. You can choose to insert a single quotation mark before the number sequence you need to enter.
So, this means you've defaulted this parameter cell to be a text data cell.
After you press Enter, you'll also see Excel's error message. You proceed similarly to the above method, ignoring this error message with Ignore Error.
For both methods above, you can select all cells with error messages simultaneously and choose to dismiss them all at once by selecting Ignore Error.
2. Adding a leading zero to the number sequence while still preserving the number data cell.
To add a leading zero to the number sequence while still preserving the number data cell, follow these steps:
First, right-click on the parameter cell where you need to enter the number sequence with a leading zero.
Next, select Format Cells from the displayed options list.
Open the Number tab in the Format Cells window that just appeared.
Continue to switch to the Special section under Category.
Select Zip Code from the Type section.
Then, enter the number sequence with leading zeros as usual.
As you can see, two numbers in the Zip Code format can still be used for calculations as usual.
Thank you for following our guide on how to add leading zeros to numbers in Excel from Mytour. We wish you success in implementing this trick.
