How to add leading zeros to numbers in Excel
To display numeric data in Excel documents with different purposes, sometimes you need to have a leading zero. Come to our tutorial, TipsMake.com will guide you how to add the number 9 to the beginning of the sequence.
Parameter cells in Excel are classified into different data cells, for example, the parameter cell contains a range of numbers, when you finish filling that parameter cell, they automatically switch to the numeric data cell type. .
When you enter a leading sequence of zeros, Excel automatically cleans up with the most accurate number for that range, which means eliminating the zero.
So adding zeros to the beginning of the sequence can be done in many ways, but you need to note that you want to keep the numeric data cell for that parameter cell or not, otherwise we can turn it. Becoming a text cell, Excel will not automatically shorten your number range.
1. Add zeros at the beginning of the sequence with the resulting parameter data text
To convert a parameter cell to a text data type, you can do the following:
Method 1: Convert the parameter cell type before filling the number range
To convert the parameter cell type into a text data box, click on the data cell or you can select an entire data array.
Next, open the Home ribbon on the toolbar, and then click Number Format in the Number section .
Then you select Text in the list just displayed.
Then you enter the sequence of zeros at the input in the converted data cell, you will see that your zero has not been omitted as before but there is a blue triangle in Excel. at the corner.
This is an Excel reminder that the data you enter is a continuous number, and it asks if you entered the wrong cell and want to return the current cell to the numeric data cell.
Of course we intentionally so, you need to click on the triangle and select Ignore Error to ignore the error that Excel mentioned, the triangle will disappear.
Method 2: Format the parameter cell immediately while entering a number range
It is not necessary to select a parameter cell and then convert before you enter the number range, you can choose how to insert apostrophes in front of the required number range.
That means you have defaulted this parameter cell to be a text field.
After you press Enter you will also see Excel error message, you follow the same way above, ignoring this error message Ignore Error .
For both of the above, you can highlight all the data boxes with error messages and then choose to remove this message at once by selecting Ignore Error .
2. Add zeros at the beginning of the sequence but keep the numeric data cell intact
To add zeros to the beginning of the sequence but keep the numeric cell intact, do the following.
First, right-click on the parameter cell you need to enter the number sequence at the beginning.
Next, select Format Cells from the displayed list of options.
You open the Number tab in the Format Cells window box that appears.
Continue to change to Special in Category .
You choose Zip Code in the Type section .
Then you enter the sequence of numbers with 0 at the beginning as usual.
As you can see, the two numbers in Zip Code form can still perform calculations as usual.
Thank you for following our article TipsMake.com on how to add leading zeros to numbers in Excel. Wish you can successfully perform the operation of this trick.
You should read it
- How to write leading zeros in Excel
- How to add zeros to phone numbers in Excel
- How to Remove Leading or Trailing Zeros in Excel
- 3 Quick ways to fix the problem of Excel losing leading zeros
- How to Prevent Excel from Removing Leading & Trailing Zeros
- The leading zero format in Excel number ranges is easy
- Instructions to stamp negative numbers in Excel
- How to display 0 in front of a number in Excel
- PI (PI Function) in Excel - How to use PI numbers in Excel
- Split numbers from strings in Excel
- How to extract numbers or text from Excel
- How to format numbers in Excel
Maybe you are interested
First success in restoring vision using stem cell treatment
Transparent solar cells help smartphones charge themselves with sunlight
Tips for formatting cells in Excel for professional spreadsheets
How to Justify Text in Cells on Excel - Adjust Text Spacing
What is a bifacial solar cell? What are the advantages?
Successfully developed 'indoor solar cells' with conversion efficiency up to 37%