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