Mid and Right

[dturgel]

Hi, I have a column of values in excel that look like this:
[TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl2916, width: 76"]11065-91[/TD]
[/TR]
</tbody>[/TABLE]
I want to add a 0 before the '-' and a 0 in between the final two numbers. I tried to record a macro to figure it out but it didn't work. Any idea how I should do this with VBA? How would you do it for just the one instead of the entire column?
Daniel

 

[BobUmlas]

Function Inserter(Rg As Range)
Dim St As String
St = Mid(Rg.Value, Len(Rg.Value) - 1, 1) & "0" & Right(Rg.Value, 1)
N = InStr(Rg.Value, "-")
Inserter = Left(Rg.Value, N - 1) & "0-" & St
End Function

 

[MARK858]

Just a different method using B2 as the cell holding the string.

Sub RepIt()
[b2] = Replace([b2], "-", "0-")
[b2] = Left([b2], Len([b2]) - 1) & "0" & Right([b2], 1)
End Sub

 

[dturgel]

Thanks Bob! How would you set up to call that function? (Sorry, I looked how to do this but I couldn't figure it out).

 

[dturgel]

Thanks Mark! How would you declare a variable to make this work for any cell / flip through cells in a predetermined range? I tried the following but it did not work:

Dim Rng As Variant
Set Rng = ThisWorkbook.Sheets("2015").Cells(i, 1)

For i = 289 To 390


= Replace(, "-", "0-")
= Left(, Len() - 1) & "0" & Right(, 1)


Next i


End Sub

 

[MARK858]

I don't know what you mean by declare a variable for it but if you want it to work on that range then try...

Sub zzz()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 289 To 390
        Cells(i, 1) = Replace(Cells(i, 1), "-", "0-")
        Cells(i, 1) = Left(Cells(i, 1), Len(Cells(i, 1)) - 1) & "0" & Right(Cells(i, 1), 1)
    Next i
    Application.ScreenUpdating = False
End Sub

or as a function then the code below and run zzz2.

Sub zzz2()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 289 To 390
    Cells(i, 1) = dddd(Cells(i, 1))
    Next i
    Application.ScreenUpdating = False
End Sub

Function dddd(Rg As Range)
[Rg] = Replace(Rg, "-", "0-")
dddd = Left(Rg, Len(Rg) - 1) & "0" & Right(Rg, 1)
End Function

 

[dturgel]

Mark,
Fantastic - thanks again!
Daniel

 

[Rick Rothstein]

I don't know what you mean by declare a variable for it but if you want it to work on that range then try...

Sub zzz()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 289 To 390
        Cells(i, 1) = Replace(Cells(i, 1), "-", "0-")
        Cells(i, 1) = Left(Cells(i, 1), Len(Cells(i, 1)) - 1) & "0" & Right(Cells(i, 1), 1)
    Next i
    Application.ScreenUpdating = False
End Sub

Since the range is fixed, here is another way to write your macro...

[table="width: 500"]
[tr]
	[td]Sub zzz()
  [A289:A390] = [REPLACE(SUBSTITUTE(A289:A390,"-","0-"),LEN(A289:A390)+1,0,0)]
End Sub[/td]
[/tr]
[/table]

 

[MARK858]

@dturgel, you're welcome but in both codes please change the 2nd

Application.ScreenUpdating = False

to

Application.ScreenUpdating = True

and then run the code again on a test page.

Apologies, I must have copied/pasted it and then forgot to amend it.
Please note it is important that you make this change.


Thanks Rick, I was 1/2 hoping that you would see this thread as I kept thinking that there should be a one-liner and more importantly be able to apply it to the range in one go.