If you enter large numbers like 003-435-556-578 in any row or column of an excel sheet, if the recently recorded number has leading zeros, excel will automatically omit the leading zeros. This action can be offending and annoying especially when you do not know how to insert back your zeros.
Large numbers like the one above may be an example of mobile phone numbers, security codes, postal codes, and credit card numbers. Since we have to record the whole numbers to avoid misleading information of errors, there is a need to keep the leading zeros in the numbers.
The zero value in any given context may mean a lot so omitting it will be an offense to the end-user of the data you just created. To keep the leading zeros in front of the numbers, we have the custom format to help us achieve that.
A couple of steps may be involved under the custom format. These steps are discussed below in depth.
Step 1
Open the data records you need to keep leading zeros in excel. If you do not have any, then open a new or a blank excel sheet on your computing device. Insert the characters you wish to work with on each column or row. An example is the one below.
In my sample above, I recorded 00523, 00546, and 02031 but all the leading zeros were removed.
Step 2
I need to get my zeros back to make my codes valid or my data accurate. The next step is to select all the Cells with the codes.
After selecting them, go to the home tab and navigate to the number group. Select the small icon on the bottom right that looks more like a drop-down arrow.
The small icon will open a window for you with numerous options. Scroll down and select on custom.
Step 3
Change the value from general in the type box to 00000 or any other number of zeros you would like to use. Click on ok to save the changes. The zeros will be added just as in the case below.
