Solved

VBA Excel Code: Add leading zeros

Posted on 2014-04-16
4
13,413 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 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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

808 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