Add or remove leading zeros in Excel and Google Sheets

  • Post author:
  • Post category:Uncategorized

When handling special numbers or texts such as identification numbers, security numbers or zip codes, it is of vital importance that we keep the leading zeros to our data.  In some cases, we might also need to add leading zeros to keep a certain specified format.

While working with Excel and Google sheets, we are able to add or remove leading zeros by changing the format, or by using functions such as TEXT, REPT or VALUE.    

Figure 1.  Final result:  Add or remove leading zeros

There are three methods to add leading zeros:

  • Format as text to keep zeros as we type
  • Convert to text using TEXT and REPT function
  • Custom number format “0000000”

There are also three methods to remove leading zeros:

  • Format cell in Number or General format
  • Convert to number using the VALUE function
  • Text to Columns Wizard

Keep leading zeros through text format

In order to keep leading zeros as we type them in a cell, we format the cell as text.  

  • Select the cell where we want to enter the value
  • Click Home tab > Number menu options > Text

Figure 2.  Text format option

  • Enter the value “0012345”  in cell B3

Figure 3.  Output: Keep leading zeros

As we enter the value in a cell formatted as text, we will be able to keep the zeros as we type them.  In the example above, once we are done typing, a yellow diamond sign appears beside the cell bearing the warning message that the number is stored as text.

Leading zeros in Google sheets

In the same manner, we can keep leading zeros in Google sheets by entering the value in text format.  Click Format tab > Number Plain text

Figure 4.  Plain text format in Google sheets

Using TEXT and REPT to add leading zeros

We can also add zeros in Excel by using the TEXT and REPT functions.  This way, we can convert the number into text without formatting.  Suppose we have the number 12345 in cell B3. We want to display it as a seven-digit number with leading zeros through either of these methods:

  • Using the TEXT function only, we enter the formula   =TEXT(B3,"0000000")
  • Using the TEXT and REPT functions, we enter the formula   =TEXT(B3,REPT(0,7))

The results in E3 and E4 both show a value with leading zeros “0012345”.  

Figure 5.  Add zeros through TEXT and REPT

Use custom format “0000000”

There is a way for us to add zeros before a number without converting it into text through custom format in Format Cells.

  • Right-click on the cell and select Format Cells

Figure 6.  Format Cells option

  • In Format Cells dialog box, select Custom under Number tab
  • In the Type: textbox, enter seven zeros “0000000

Figure 7.  Format Cells dialog box

Once we type the custom format, the Sample text box above will show a preview of our number.  One zero “0” in custom format represents one digit, while an extra zero is displayed as “0”. Since our number is composed of five non-zero digits “12345”, the two extra zeros will remain as zeros.  Finally, the resulting number will be “0012345”.

How to remove leading zeros?

Conversely, we can remove leading zeros through these methods:

  • Format cell in Number or General format

By simply changing the format from Text or Custom format into Number or General format, we can easily remove the leading zeros in a cell. Below image shows the different formats and their effect when applied to the number “0012345”

Figure 8.  Format cells to add or remove zeros

  • Use the VALUE function

The VALUE function converts a text into a numeric value.  In cell D3, we enter the formula =VALUE(B3). As a result, we have removed the leading zeros and come up with the numeric value “12345”.  

Figure 9.  Remove leading zeros using VALUE function

  • Text to Columns Wizard
    • Select the cell containing the text value with leading zeros
    • Click Data tab > Text to Columns > Delimited Tab General Finish

Figure 10.  Convert Text to Columns Wizard dialog box

Figure 11.  Delimiters preview

Figure 12.  Data format preview

Ultimately, what we have done here is to set the format to General for all delimited text in our worksheet.  The result is the same, and we are able to successfully remove the leading zeros.  

Figure 13.  Output: Remove leading zeros