CODEWITHSUNDEEP
excelvba
smakfactor1
smakfactor1

Reputation: 71

Excel VBA - add zero's inbetween two values

I've done quite a bit of searching around for this one...and I'm not getting anywhere.

I have a spreadsheet(specific column) with values such as:

42153-95    
54126-3    
13613-6331    
16136-336

My goal is to add zero's after the - and before the existing #'s(to 4 places). Like:

42153-0095    
54126-0003    
13613-6331    
16136-0336

I've tried a a lot of different options within the quotes of NumberFormat:

Worksheets("Sheet1").Columns("C"). _ NumberFormat = "00000-0000"

No luck so far. :(

Any help would be greatly appreciated.

Thanks!

Upvotes: 0

Views: 99

Answers (3)

Gary's Student
Gary's Student

Reputation: 96791

Select the cells you wish to process and run:

Sub dural()
    Dim r As Range

    bry = Array("0000", "000", "00", "0", "")
    For Each r In Selection
        ary = Split(r.Value, "-")
        ary(1) = bry(Len(ary(1))) & ary(1)
        r.Value = Join(ary, "-")
    Next r
End Sub

Before:

enter image description here

and after:

enter image description here

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

As @tigeravatar stated it can be done with a formula. With the Evaluate function we can use an array form of the formula he gave in his comment.

You can apply this to your values in column C:

Worksheets("Sheet1").Range("C1:C4").Value = Worksheets("Sheet1").Evaluate("=INDEX(LEFT(C1:C4,6) & TEXT(--MID(C1:C4,7,LEN(C1:C4)),""0000""),)")

If your range is dynamic and you have the final row in a variable like lstrow you can replace all the C4 with C" & lstrow & "

Worksheets("Sheet1").Range("C1:C" & lstrow).Value = Worksheets("Sheet1").Evaluate("=INDEX(LEFT(C1:C" & lstrow & ",6) & TEXT(--MID(C1:C" & lstrow & ",7,LEN(C1:C" & lstrow & ")),""0000""),)")

Upvotes: 1

cyboashu
cyboashu

Reputation: 10443

Sub testFunc()

    MsgBox addZero("54126-3")

End Sub

'/ Function to add Zeros

Public Function addZero(strVal As String) As String

    Dim arrTemp
    Dim strTemp

    arrTemp = Split(strVal, "-")

    strTemp = arrTemp(0) & "-" & String(4 - Len(arrTemp(1)), "0") & arrTemp(1)

    addZero = strTemp

End Function

Upvotes: 1

Related Questions