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

Quite correctly, a leading zero will only stick on a number if you forcefully convert the entire thing to string (which you do by prepending the `'`). Apply the number formatting instead. — GSerg, Jun 01 '22 at 08:11

Pᴇʜ · Answer 1 · 2022-06-01T08:56:58.513

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

edited Jun 01 '22 at 08:56

answered Jun 01 '22 at 08:48

Pᴇʜ

56,719
10
49
73

Hi there, sorry i'm newbie and i still think in sql. My purpose is not to convert cell into numeric one before leading 0. I need a vba can format to 7/8/9/10 digit adding leading 0 before if the cells/column have a number. Is this possible? – MrSamael Jun 01 '22 at 13:36
1

@MrSamael I don't understand what you mean? Are those cells formatted as numeric or are they formatted text that contain number digits? If they are text and you want to keep them text you need to add the `'` infront when adding the zeros. Otherwise Excel will automatically convert them to numeric numbers. – Pᴇʜ Jun 02 '22 at 06:35

How to add a leading 0 to column number?

1 Answers1