Solved

VBA Excel Code: Add leading zeros

Posted on 2014-04-16
4
14,504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 81

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 81

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 81

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 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