VBA Excel Code: Add leading zeros

I need code to add 2 leading zeros to any number (formatted as text) that is 6 digits long.  The numbers reside in column A.
JAMES125Asked:
Who is Participating?
 
byundtCommented:
Based on your latest request, here is another macro that will return the results as text. Two alternatives are shown. In one, the resulting values are padded with zeros until they are all eight digits long (unless original number was more than eight digits). In the other, two leading zeros are added to the original number. Please pick the statement that best fits your needs when testing.
Sub LeadingZeros5()
Dim cel As Range, rg As Range
Dim d As Double
Application.ScreenUpdating = False
Set rg = Range("A2")        'First cell in range to be converted
Set rg = Range(rg, rg.Worksheet.Cells(Rows.Count, rg.Column).End(xlUp)) 'All the data in that column
rg.NumberFormat = "@"
On Error Resume Next
For Each cel In rg.Cells
    If IsNumeric(cel.Value) Then
        d = Val(cel.Value)
        'cel.Value = Format(d, "00000000")       'Eight digit number with leading zeros as required
        cel.Value = "00" & d                 'Variable length number with two leading zeros
    End If
Next
On Error GoTo 0
End Sub

Open in new window


If the above macro is still not doing what you want, could you please post a workbook that shows sample data and expected results? I want to see both the variation present in the original data as well as how you'd like to handle that variation.

There is no .TextFormat property of a cell, only its .NumberFormat property. If the .NumberFormat property equals "@" then the contents are displayed as text, however.
2
 
byundtCommented:
Here are three different ways of doing it in code:
Sub LeadingZeros()
Dim rg As Range
Set rg = Selection
rg.NumberFormat = "00000000"
End Sub

Sub LeadingZeros2()
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
Set rg = Selection
rg.NumberFormat = "@"
For Each cel In rg.Cells
    cel.Value = "00" & cel.Value
Next
End Sub


Sub LeadingZeros3()
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
Set rg = Selection
rg.Columns(1).TextToColumns Destination:=rg.Cells(1, 1), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
rg.NumberFormat = "00000000"
End Sub

Open in new window

1
 
byundtCommented:
And yet another macro:
Sub LeadingZeros4()
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
Set rg = Selection
For Each cel In rg.Cells
    If Len(cel.Text) = 6 Then
        cel.Value = Val(cel.Value)
        cel.NumberFormat = "00000000"
    End If
Next
End Sub

Open in new window


I haven't been recommending one method over another because you didn't post a sample workbook, so it isn't clear which method will better match your situation.

As written, all four macros work on a range of cells that you select before running the macro. If instead you want to apply the code to a range of cells starting in A2, just replace the statement Set rg = Selection with:
Set rg = Range(Range("A2"),Cells(Rows.Count,1).End(xlUp))

Open in new window

0
 
JAMES125Author Commented:
I need to format it as text because I will be concatinating the cell with other data.  Is there a "cel.TextFormat" option.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.