Adding a missing 0

New Contributor

have this number format:

12345-678-91

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

3 Replies

@natvo 

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

 

00000"-0"000"-"00

 

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.....

@natvo 

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.