Adding a missing 0

New Contributor

have this number format:


need to tell Excel to add a 0 in front of the number 6 to make if a 4-digit do i do that in excel?

3 Replies


If the cell contains a number and if it is just for display purposes, you could create and apply the custom number format




Otherwise, you could create the following macro. Select the cells to be processed before running it.

Sub Add0()
    Dim rng As Range
    Application.ScreenUpdating = False
    For Each rng In Selection
        If rng.Text Like "[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9]" Then
            rng.Value = Left(rng.Text, 6) & "0" & Right(rng.Text, 6)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
thanks...haven't done macro before.....


Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy and paste the code into the module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Select the cells.

Press Alt+F8 to display the Macros dialog.

Select the macro and click Run.