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 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
May be interested
- How to extract numbers or text from Excelmicrosoft excel is great for working with both numbers and text, but if you use both in the same cell, you may have some difficulties. fortunately, you can extract numbers or text from cells to work with your data more efficiently.
- How to format numbers in Excelhow to format numbers in excel in the process of working, processing data in excel sometimes you need to format numbers in excel so that data can display the number format in accordance with the requirements to be processed. you do not know how to format numbers in ex
- Some experience in Excel spreadsheetsin excel there are many good tips that only those who have ever discovered, please introduce to you some of the experience in excel.
- How to convert negative numbers to positive numbers Excelto convert negative numbers to positive numbers and vice versa on excel, you only need to perform a very simple operation.
- How to convert Apple Numbers documents to Microsoft Excelnumbers is spreadsheet software specifically for apple devices both on phones and on computers. basically, apple numbers works similar to microsoft excel, so you can completely convert documents between these two toolkits to serve each specific working situation.
- Instructions for converting numbers to words in Excelto convert numbers to words in excel, you will use a support tool, instead of typing by hand, which is not accurate and takes a lot of time to do. below are some ways to convert numbers to words in excel.
- How to convert numbers into words in Excel?in excel, entering and typing amounts is very tiring, sometimes confusing. so why do this work faster but still have to ensure accuracy? tipsmake.com will introduce you to a support tool, or more accurately, an additional add-in for microsoft excel, very convenient for accounting, finance, people who have to work with big amounts ...
- How to round whole numbers in Excelhow to round whole numbers in excel in excel there are many mathematical functions used to round numbers. today software tips will guide you some functions that can be used to round whole numbers in excel.
- Do not round numbers in Excel, turn off rounding in Excelrounding numbers in excel is a handy function for calculating data, but sometimes it also makes you feel unnecessary troubles. to turn off the function of rounding in excel you can follow the following instructions
- Convert numbers to text in Excelconvert numbers into words in excel - regularly process and calculate data with excel spreadsheets, if the data is related to currency, the number of words you want to convert will be many, vntools software to help you change numbers into words quickly and efficiently