How to add a leading 0 to column number?

Question

I'm trying to add a leading 0 to a numeric column.

The problem is not all numeric columns are recognized as they are.

Sub AddZero()
    Dim MyRange As Object
    Dim celle As Range
    Dim i As Integer
    Dim Result As String
    Dim StrLen As Integer
    Dim cycle As Integer

    Set MyRange = Selection
    Selection.EntireColumn.Select
    Selection.Copy
    Selection.Insert
    
    Set celle = Selection

    zeri = InputBox("How much 0?")

    Length = Val(zeri)

    For Each cella In celle
    
        If IsNumeric(cella) Then
        
            StrLen = Len(cella)
            cycle = Length + StrLen
        
            For i = 1 To Length
                If cycle > StrLen Then
                    If Not IsEmpty(cella) Then
                        Result = "0" & cella.Value
                        cella.Value = Trim(Result)
                        StrLen = Len(cella)
                    End If
                End If
            Next i

        End If
    Next cella
    MyRange.Select
End Sub

I copy and paste the column, and then, I lead an amount of 0 to it.

It works only if I put a ' before cell value even if I remove If IsNumeric(cella).

Answer 1

You can just change the number format to the amounts of digits you want.

Option Explicit

Public Sub FormatLeadingZeros()
    Dim RetVal As Variant
    RetVal = Application.InputBox(Prompt:="How many digits?", Type:=1)  ' Type:=1 allow numbers only
    
    If VarType(RetVal) = vbBoolean And RetVal = False Then
        ' user pressed cancel
        Exit Sub
    ElseIf CLng(RetVal) < 0 Then
        ' user entered number smaller than 0
        Exit Sub
    End If
    
    With Selection.EntireColumn
        ' set the number format to the amount of digits entered
        .NumberFormat = String(CLng(RetVal), "0")
    End With
End Sub

For example if you have the following numeric data

and you enter 3 into the input box, it will turn it into

but keep the numeric values (so you can still calculate with them).