Excel automatically removes leading zeroes (those placed to the left of an integer and those placed at the end of a decimal number). If this feature has a negative impact on the values you need to store in your file, for example numbers such as zip codes, you can import the values as text, so they are displayed correctly. If you need to perform mathematical calculations you will need to format the values using a custom number format, so that leading zeroes are not eliminated. Learning how to use custom number formats correctly and fully takes some time, but learning the basic concepts of displaying zeros at the beginning and end of a number will be fairly straightforward.
Steps
Method 1 of 2: Store Numbers as Text
Step 1. Add an apostrophe at the beginning of the number
If you only need to change some cells of the document, you can solve the problem by manually acting on one cell at a time. Edit them individually by entering an apostrophe (') and typing the number exactly as it should appear inside the cell. The apostrophe symbol instructs Excel to store the content you type as text within the selected cell. This means that the number you entered cannot be used in mathematical, statistical or formulas that involve calculations.
If you need to use these numbers within formulas or functions, read the second method of the article
Step 2. Save the contents of a large database in the form of text
When you import numeric data into an Excel file, all non-significant zeros are permanently deleted. The simplest and fastest way to overcome this problem is to delete the incorrect data and repeat the import phase. Resume the original database and save the corresponding content in the form of a CSV file or plain text (file with the extension ".txt").
Step 3. Create a new workbook
At this point you have to import the database data into a new Excel file in the form of simple text. This means that you will not be able to use numerical values within mathematical formulas and functions. However, this will preserve the original formatting of the data.
This method allows you to import some data in the form of text, others as numerical values into Excel. In this case, however, only the columns indicated as text will still show leading zeros
Step 4. Launch the Excel Data Import Wizard
Click on the Data tab of the program ribbon, locate the "Load External Data" group, then click the Text or From Text button.
Step 5. Import the data from the external text file
Select the text document containing the data using the "Select a File" screen. Follow the instructions that will appear on the screen to complete steps 1 and 2 of the wizard.
Step 6. Format the columns as text
Within the screen of step number 3 of the "Text Import Wizard" you should see a preview of how the data will appear in the Excel sheet. Click on a column header to select it, then choose one of the formatting options listed in the "Data format by column" box to set the format of the data stored in the chosen column. Specifically, to prevent leading zeros of columns containing telephone numbers or similar data from being deleted, select them and choose the Text format. At this point you can complete the import of the data and load it into the Excel sheet preserving all zeros where necessary.
Method 2 of 2: Create a Custom Number Format
Step 1. Select the cells you need to format
A custom number format is intended to tell Excel what the exact format in which the data will be displayed must be, which in this case must include leading and trailing zeros. Start by selecting the columns, rows or range of cells you need to format.
Step 2. Open the formatting window
Click on the Home tab of the Excel ribbon and locate the "Numbers" group. If you are using the Windows version of Excel, click on the button to the right of the word "Number" (it has a small down arrow). If you are using the Mac version of Excel, click on the drop-down menu indicating the "General" format (by default) and select the Custom… item.
If you are using an older version of Excel without the ribbon, click on the Format menu, select the Cells item and click on the Number tab
Step 3. Create a custom number format
If you are using Excel 2013 or 2016, select one of the formats listed. When you customize one of the predefined number formats, Excel will automatically create a new one without changing the original one. If you are using another version of Excel, select the Custom option from the box listing the available number formats. Locate the text field called "Type", where you will need to enter the format you want.
If the data you need to format represents calendar dates, phone numbers, or other commonly used data, check out the list of predefined formats - you may be able to use one of those offered by Excel without the need to create your own
Step 4. Add leading zeros to a number
Custom formats use special characters to tell Excel the number and type of digits to be displayed. The zero character (0) indicates to the program that it should display the number zero in the position it appears in the format mask if there are no useful values in the corresponding position of the number to be formatted. For example, the "000" format adds leading zeros for hundreds and tens as needed. In this case the number 97 will be displayed in the format "097", while the number 3 will be displayed as "003".
Digits placed to the left of a decimal number are displayed in each case. Even though the sample custom number format only has three-digit numbers, the value 3.750 will be displayed correctly as 3.750
Step 5. Specify the number of decimal places and the number of trailing zeros
When using a custom number format, unless otherwise specified, all digits after the decimal point will be truncated. To change this Excel behavior, add the decimal separator to the end of the number format followed by the number 0 (and one of the special characters described below). For example, the numeric format "000, 000" rounds the numeric value to the third decimal place and adds the necessary zeros. Here are some examples of how the "000.000" number format would be applied:
- The number 13, 1 would be displayed as 013, 100.
- The value 95.001 would be displayed as 95.001,000.
- The number 5, 0057 would become 5, 006.
Step 6. Use the hash character to prevent leading zeros from being displayed
The # symbol is another special character that can be used within custom number formats. It prevents the display of the leading and trailing zeros of a number based on where it is placed within the layout mask. For example, the custom number format "00, ##" allows the display of the leading zeroes placed at the beginning of a number, but not those placed in the decimal part:
- The value 5, 8 would become 05, 8.
- The number 2, 71 would become 02, 71.
- The value 15.0 would become 15.
- The number 38, 315 would become 38, 32.
Step 7. Add leading zeros as text
Excel will handle any content enclosed in quotation marks as plain text when specified in a custom format and will display exactly as typed. This step is useful if for example each numeric field in the original database has a leading pair of zeros. To correctly display these values, you will need to set up Excel to use the following custom number format "00"#.
If your data also has a decimal part, you can avoid automatic rounding by using the following number format "00"#.## instead of the previous one. In this case use as many "#" or "0" characters as needed based on the number of decimal places you need.
Step 8. Align cell contents based on decimal separator using question mark
If your goal is to align the cell contents of a column by the decimal point, you don't need to add zeros to the beginning or end of each number; just use the "?" within the custom number format. Excel will automatically add the number of empty spaces necessary to uniform the alignment of the contents of all the cells of the column. Here are some examples:
- The format "?, ???" rounds the value to the third decimal place without adding any zeros and aligns the cell contents according to the decimal point.
- The format "000,?" adds as many leading zeros as needed, up to hundreds, then rounds the value to the first decimal place and aligns the cell contents according to the decimal point.
- The "?, 00" format rounds the numeric value to the second decimal place by adding trailing zeros if necessary and aligns the cell contents according to the decimal point.
Advice
- After creating a custom number format, it will automatically be stored in the list of formats available within the workbook in question.
- Excel has a maximum limit of 15 digits for representing numeric values. Any number that has more than 15 digits will be rounded, regardless of the number format you choose to use. To work around this problem with phone numbers, credit cards, and other numeric codes that do not represent a real value, store them in the cells of the sheet as plain text.
- The asterisk symbol "*" is used within Excel as a special character to customize formats or commands. This symbol is used to tell the program to repeat a character as many times as necessary to completely fill a cell based on its size. For example, the format "* 0 #" (without quotation marks) repeats the zero character until the cell is filled while the format "#. # * 0" (without quotation marks) rounds the number to the second decimal place and then adds the necessary number of zeros until the cell in which it is stored is filled.
