The result in column D is text – the number is embedded in a text string with the correct number of zeros. Read below for details and the steps needed to apply Option 2. There are two basic ways to solve this problem: (1) convert the number to text with leading zeros (2) apply a custom number format to display the number with leading zeros. The best approach depends on your needs. If numbers really should be text values, use Option 1. If numbers need to remain numeric values, use Option 2. Both options are easy to implement, but Option 1 is more flexible.
Option 1 - convert to text
A simple way to add leading zeros to a number is to use the TEXT function. The TEXT function returns a number formatted as text, using the number format provided. In this case, we want the final result to have five characters total, so the number format includes five zeros: “00000”. This number format is used directly in the TEXT function as the format_text argument: The result is the text string “00127”. Note that Excel will automatically align text values to the left, but you can manually set alignment as desired. To replace the original numbers with the converted text values: first copy the converted values, then select the original numbers and use Paste Special > Values to overwrite the original numbers. Related video: How to do in place changes with Paste Special
Option 2 - apply number format
Another way to add leading zeros to a number is to apply a custom number format to display the numbers with leading zeros. The key thing to understand with this option is that the numeric values underneath are not affected. Applying a number format only changes the way the numbers are displayed in Excel. To apply a number format to display leading zeros, select the values in F5:F16 and use the shortcut Control + 1 to display the Format Cells window:
Then navigate to the Number tab, select “Custom” and enter 00000 in the “Type” input area:
Note the Sample area will show an example of the format applied to the first cell in the selection. When you click “OK” the number format is applied and numbers are displayed with leading zeros. Note the formula bar will continue to show the numeric value in its original form:
Summary
Both options above work well for adding leading zeros to a number. The key difference is that Option 1 results in text, while Option 2 preserves the number. Be aware that you may need to cater to this difference in other formulas. For example, if you are performing a lookup operation with VLOOKUP or INDEX and MATCH, you must match the lookup value to the data. The formulas below show how this difference affects a VLOOKUP formula: More on VLOOKUP here.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
