Home » How to Add a Zero In Front of a Number in Excel

How to Add a Zero In Front of a Number in Excel

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…

  1. Select cells
  2. On the Home tab of the ribbon, in the Number group, select Text in the format drop-down
  3. 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…

  1. Select the cells
  2. Use the shortcut key CTRL 1 to open the Format Cells dialog
  3. Make sure the Number tab is selected
  4. In the Category list, select Custom
  5. In the Type box, type 000. 000 will return a minimum of three characters eg 012. 00 would return a minimum of 2 characters
  6. Click on OK to confirm