Keep Or Delete Leading Zeros In Excel

When you type some numbers in Excel table, you may find that sometimes you need the leading zeros in the numbers but sometimes not. So you may want to make it clear how or keep or delete leading zeros in Excel. Here i would show you.

Keep Leading Zeros With Format Cells

Step 1: When you want to keep leading zeros in a range of cells, you should first select the range of cells and right click to bring up an option list. Last, select the Format Cells option.

Step 2: When the Format Cells dialog pops up, just find the Number tab and choose the Text in the category pane.

Then you can see that the number you have typed in the cells has kept the leading zeros.

Remove the leading zeros in Excel

When you want to remove the leading zeros in Excel table you should follow the under ways.

Way 1: 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.

Way 2: Delete the leading zeros in Excel by VBA

Step 1: After launching the Excel, you should just press the Alt+F11 to bring up the Microsoft Visual Basic for Applications window.

Step 2: Select the target worksheet and click Insert>>Module.

Step 3: Type the under words in the coming module section and then click Run button.

Sub DeleteZero()

'updateby20140616

Dim Rng As Range

Dim WorkRng As Range

On Error Resume Next

xTitleId = "Deleteleadingzero"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

WorkRng.NumberFormat = "General"

WorkRng.Value = WorkRng.Value

End Sub

Step 4: Then a new dialog labelled with Deleteleadingzero would pop up and you can select the range of cells you want to delete the leading zeros with. Last, click OK. Then you would fine that the leading zeros have been deleted in the corresponding cells.