Solved

VBA Excel Code: Add leading zeros

Posted on 2014-04-16
4
15,194 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

617 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