How to keep or delete leading zeros in Excel?

In general, when we type a number which contains leading zeros like 00032423 into a cell, it may show as 32423. And how can you keep these leading zeros in Excel? And in reverse, how can you delete the leading zeros when you do not need to show them?

Keep leading zeros with Format Cells

Delete leading zeros

Delete leading zeros with Kutools for Excel by clicks

Add/Delete same leading zeros with Kutools for Excel

---

Keep leading zeros with Format Cells

If you want to keep the leading zeros in Excel, you just need to format the cells as Text formating befor you typing the data.

1. Select the range cells you want to type the data with leading zeros into, and right clcik to select Format Cells from the context menu. See screenshot:

2. In the Format Cells dialog, click Number tab, and select Text from the Category pane. Click OK to close the dialog. See screenshot:

Now when you type numbers with leading zeros into the range cells, the leading zeros will be retained in the cells. See screenshot:

Note: This method cannot work if you format cells as text after typing numbers.

---

---

Delete leading zeros

If you want to delete the leading zeros in Excel, you have four methods to solve it.

Method 1 Format cells as number formatting

Select the range you want to type number without showing leading zeros, and right click to click Format Cells to open Format Cells dialog, and select Number from the Category pane, then click OK.　

Note: This method cannot work if you format cells after typing number.

Method 2 Use a formula

Select a blank cell next to the range cells you have typed the numbers with leading zeros, and type this formula =VALUE(A1) (A1 indicates the cell contains numbers with leading zeros) into it. And press Enter button, now the number without leading zero is shown in the formula cell. See screenshot:

Method 3 Run a VBA

1. Press Alt + F11 to display the Microsoft Visual Basic for Applications window.

2. In the window, click Insert > Module to show a new module window, then copy the following VBA code into the module window.

VBA: Delete leading zeros in Excel

Sub DeleteZero()
'updateby20140616
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
WorkRng.NumberFormat = "General"
WorkRng.Value = WorkRng.Value
End Sub

3. Click Run button, and a KutoolsforExcel dialog pops out for you to select a range to delete leading zeros. See screenshot:

4. Click OK. Then the leading zeros in selection are removed.

Method 4 Use Convert to Number feature

Select the range cells which are filled by numbers with leading zeros, click the warning icon besides the selection, and then select the Convert to Number from the context menu. See screen shot:

---

Delete leading zeros with Kutools for Excel by clicks

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

If you have Kutools for Excel, you can quickly delete all leading zeros from texts with two clicks by its Remove Leading Zeros utility

Select the text you want to delete the leading zeros, and then click Kutools > Text > Remove Leading Zeros. See screenshot:

Then a dialog pops out to remind that the leading zeros have been removed from how many cells. See screenshot:

Click OK to close the dialog.

---

Add/Delete leading zeros with Kutools for Excel

If you want to add or delete the same number of leading zeros to a cell, you can apply Kutools for Excel's Add Text and Remove by Position utilities, one can help you add same number of leading zeros to cells, one help you remove same number of string from left of the cells.

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

Add same number of leading zeros

1. Firstly, you need to format the cells as Text by selecting them and clicking Home and go to Numeric group to select Text from the drop down list. See screenshot:

2. And then keep these cells selected, and click Kutools > Text > Add Text. See screenshot:

3. In the Add Text dialog, type the zeros you want to add into the textbox of Text, and check Before first character option, and you can preview the adding result in the right pane. See screenshot:

4. Click Ok or Apply, now the same number of leading zeros are added into cells.

Delete same number of leading zeros

1. Select cells you want to delete the leading zeros, and click Kutools > Text > Remove by Position. See screenshot:

2. In the popping dialog, type the number of zeros you want to remove, check option, and you can preview the result in the Preview pane.

3. Click Ok or Apply, now the leading zeros are removed at once.

---

Relative Articles:

• Delete drop down list in Excel
• Delete one or all pivot tables in Excel