Microsoft Excel automatically removes leading zeros for numbers, because the zeros are unnecessary for calculations or precision. There are times, however, when these first-digit zeros are necessary, such as when entering product IDs, ZIP codes or social security numbers. In such cases, you need to override Excel's default formatting behavior. Configuring a custom number format through the Format Cells dialog displays leading zeros and ensures Excel still recognizes the figures as numbers. However, you can also use text formatting through the Format Cells dialog, the Text or Concatenate functions or the Text import feature to add leading zeros.
Video of the Day
Custom Number Formats
Right-click on selected cells, rows or columns and then choose "Format Cells." On the Numbers tab, select the "Custom" category and enter zeros in the Type field; the number of zeros you enter correspond to the number of digits that should be displayed; if the cell contains fewer digits than the number of zeros you configured, leading zeros are added. You may also include formatting symbols such as dollar signs, commas, hyphens, apostrophes and decimal points. As an example, if you configured "000-00-000" (without quotes) for a social security number and the cell only contained "9," the output becomes "000-00-009." Another consideration is that Excel allows for only 15 significant figures before it rounds the number, so if your number exceeds 15 digits, excluding leading zeros, you must use a text format.
The Text Format
Text formats enable you to enter any data in a cell, including leading zeros, without Excel changing the values. Simply choose the "Text" category in the Format Cells dialog. The text format, however, will not return leading zeros that Excel already removed. If you need to add leading zeros to text or numbers, retype the value as text or use the Text or Concatenate function. You can also format a number as text in an individual cell by adding an apostrophe before the number, such as: '000-12-345 Numbers formatted as text can still be used in calculations, as long as they don't include additional text elements, such as characters or hyphens.
The Text Function
The Text function enables you to reference text or numbers and then specify a format for the output. This function uses the structure "=Text(value,format)." The value can be any cell reference or number, and the format follows the same structure as configuring custom numbers, except the format is surrounded in quotes. As an example, the following formula converts the number in cell A1 into a 9-digit social security number:
Custom number formats and the Text function specify a specific number of digits, but the number of leading zeros vary with the number of digits entered, excluding leading zeros. However, the Concatenate function enables you to add a specific number of leading zeros, regardless of how many digits are included in the source cell. Use the structure "=Concatenate(zeros,A1)" and enter the zeros surrounded by quotes. As an example, to add three zeros to the text or number in A1, use the formula:
You may also concatenate using the ampersand with the structure "=zeros&A1," such as:
When you open a text file, Excel automatically starts the Text Import Wizard. Using the default settings, Excel recognizes numbers and removes leading zeros. To prevent this behavior, select the number columns in Step 3 and choose "Text" in the Column Data Format field. This forces Excel to treat the numbers as text before it begins importing data, which means the leading zeros are preserved.