Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Add or remove leading zeros in Excel and Google Sheets

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

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc