In this tutorial, I am going to look at how to add a zero or multiple zeros in front of a number in Excel. When you want to store a number with a leading zero, it needs to be stored as a text value, as numeric values cannot start with a zero. We will look at four different methods.
METHOD 1: Prefix your number with an apostrophe – this forces the number to be stored as text. This method is fine for one or two cells. If you have many cells, try another method.
METHOD 2: Method 2 involves formatting cells prior to entering your values. To use this method…
- Select cells
- On the Home tab of the ribbon, in the Number group, select Text in the format drop-down
- Enter numbers and they will retain their leading zeros
METHOD 3: Method 3 uses a formula to generate a sequence of numbers, all of which contain three characters. This might be useful if you wanted to number rows 001 – 100, for example.
The formula for this method is =RIGHT(“00″&ROW(A1),3)
METHOD 4: Method 4 uses custom formatting to add leading zeros to existing values. To use this method…
- Select the cells
- Use the shortcut key CTRL 1 to open the Format Cells dialog
- Make sure the Number tab is selected
- In the Category list, select Custom
- In the Type box, type 000. 000 will return a minimum of three characters eg 012. 00 would return a minimum of 2 characters
- Click on OK to confirm
