Excel VBA - add zero's inbetween two values

Question

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:

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

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!

Does this have to be VBA? You can do this with a formula: =LEFT(A1,6)&TEXT(MID(A1,FIND("-",A1)+1,LEN(A1)),"0000") and then just copy -> paste special -> values — tigeravatar
– tigeravatar, Commented Jul 13, 2016 at 18:01

cyboashu · Accepted Answer · 2016-07-13 18:01:53Z

1

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

answered Jul 13, 2016 at 18:01

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Craner · Accepted Answer · 2016-07-13 18:03:56Z

1

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""),)")

answered Jul 13, 2016 at 18:03

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Comments

Gary's Student · Accepted Answer · 2016-07-13 18:17:54Z

1

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:

and after:

answered Jul 13, 2016 at 18:17

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

4 Comments

smakfactor1 Over a year ago

I'm liking your solution the best so far, however it is blowing up pretty commonly. It seems to work half the time...then continuously dies on this line: ary(1) = bry(Len(ary(1))) & ary(1) Interestingly enough, when it blows up, if I click stop debugging, and try again it will work momentarily, then have the same error shortly after.

Gary's Student Over a year ago

Data must have the dash and zero to four characters after

smakfactor1 Over a year ago

Blew up on the 544117-30 porttion of this data: 454117-0120 454117-0070 454117-0040 454117-0020 454117-30 454107-010 454117-080 454107-020 454868-020 454868-050

smakfactor1 Over a year ago

All in cells in the same Column. It did the first 4 successfully, stopped on the 5th

Collectives™ on Stack Overflow

Excel VBA - add zero's inbetween two values

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related