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
tab >*Home*menu options >*Number**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
dialog box, select*Format Cells*under*Custom*tab*Number* - In the
: textbox, enter seven zeros “*Type*”*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
tab >*Data*>*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