Solved

VBA Excel Code: Add leading zeros

Posted on 2014-04-16
4
12,705 Views
Last Modified: 2014-04-17
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.
0
Comment
Question by:JAMES125
  • 3
4 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 40004958
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

0
 
LVL 80

Expert Comment

by:byundt
ID: 40004971
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
 

Author Comment

by:JAMES125
ID: 40006378
I need to format it as text because I will be concatinating the cell with other data.  Is there a "cel.TextFormat" option.
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 40007132
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now