CODEWITHSUNDEEP
vbaexcel
Bill
Bill

Reputation: 31

Excel Macro for Leading Zeros with varying conditions

I'm working on a macro to automate the process of preparing a large number (several thousand) excel workbooks into a standard format for importing into a database.

One issue is adding leading zeros to some number/letter combinations.

I have a temporary solution at the moment but one key fault it has is that it requires user input via dialog boxes to handle the procedure. My goal was to eventually run the macro from commandline as a vbs.

It seems to me that the conditions I have are fairly ordinary but I am stumped on how to go about creating the macro code to handle it. Perhaps it would be better implemented in Access during the import workflow. However, having the excel files in the proper format is of additional benefit and why I decided to pursue a macro solution.

There are two columns that need to be manipulated, both are set to text format by the macro.

Column A is a building number and needs to be 4 digits in length. Currently they are formatted without leading zeros. Some buildings have a single or combination of letters following the number.

Some examples: 7, 76, 399, 1010

Some with letters: 76A, 93B, 812W

A few are quirkier: 76A-G, 812A-S

These need to be reformatted to have leading zeros so: 0076A-G, 0007, 1000A, etc.

Column B is a filing number and is almost identical in nature to Column A with but additional details: 76-X-001, 76A-X-023, 76A-X-R-005. These need to become 0076-X-001, etc. Only the numbers before the first '-' need leading zeros, also to 4 digits in length.

Currently I'm using two user input boxes at the start of the macro to set variables with the facility # and the leading zeros, than using find and replace to perform the update on the two columns. This works just fine but has to be run for every number in the file. Most files have only one or two numbers but some have 4-5. It also means I can't automate this entire process and will need someone to sit and manually run the macro for each file.

Anyone care to point me in the direction of a solution for automating this? I looked at other leading zero solutions but they all seem to be aimed at numeric-only situations.

Upvotes: 0

Views: 5968

Answers (2)

KISS
KISS

Reputation: 81

For column A

=RIGHT("0000"&A1,4)

For Column B

=RIGHT("0000"&LEFT(B1,FIND("-",B1,1)),5)&MID(B1,FIND("-",B1,1)+1,LEN(B1))

To do it in place for the whole range:

Sub TestFormatNumbers()
    Dim wsMaster As Worksheet
    Dim rgCellsToFormat As Range

    Set wsMaster = ActiveSheet
    Set rgCellsToFormat = wsMaster.Range("A1:A100")

    With rgCellsToFormat
        .NumberFormat = "@"
        .Value = Evaluate("=IF(ISTEXT(" & _
            .Address & "),RIGHT(""0000""&LEFT(" & _
            .Address & ",FIND(""-""," & .Address & ",1)),5)&RIGHT(" & _
            .Address & ",LEN(" & .Address & ")-FIND(""-""," & _
            .Address & ",1)),"""")")
    End With
End Sub

Upvotes: 0

Bill
Bill

Reputation: 31

I've got a working solution now. Special thanks to Teeroy.

Sub Change_Number_Format_In_String()
Dim iFirstLetterPosition As Integer
Dim sTemp As String
For Each c In Range("A2:A100")
   If Len(c) > 0 Then
    iFirstLetterPosition = Evaluate("=MATCH(TRUE,NOT(ISNUMBER(1*MID(" & c.Address & ",ROW($1:$20),1))),0)")
    sTemp = Left(c, iFirstLetterPosition - 1) 'get the leading numbers
    sTemp = Format(sTemp, "0000") 'format the numbers
    sTemp = sTemp & Mid(c, iFirstLetterPosition, Len(c)) 'concatenate the remainder of the string
    c.NumberFormat = "@"
    c.Value = sTemp
End If
Next
End Sub

Upvotes: 1

Related Questions