Adding Text to existing text string macro

[kstate1988]

What I need, is a macro that can look in Column D and add “/” and “R” to the existing 7 character text string.

Cell (2D) has what the end result should be for all cells in column D, once the macro runs.

	Dealer Description	Matched
			235/65R16
			2757018
			2256016
			2356016
			2457017
			2256016

<tbody>
</tbody>

This is what I have at this time.

Sub arranges()
Dim str As String
str = ActiveCell.Value
ActiveCell.Value = Left(str, 3) & "/" & Mid(str, 4, 2) & "R" & Mid(str, 6, 2) _
End Sub

Any help you can provide would be greatly appreciated.

 

[Jonmo1]

Try

Sub arranges()
Dim str As String, c As Range, lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("D2:D" & lr)
   str = c.Value
   c.Value = Left(str, 3) & "/" & Mid(str, 4, 2) & "R" & Mid(str, 6, 2)
Next c
End Sub

 

[kstate1988]

Dude! You ROCK!

That works great.

 

[Rick Rothstein]

Here is another macro using no loops that will also do what was asked for...

[table="width: 500"]
[tr]
	[td]Sub AddSlashAndR()
  Dim Addr As String
  Addr = "D2:D" & Cells(Rows.Count, "D").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(@="""","""",REPLACE(REPLACE(@,6,0,""R""),4,0,""/""))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[Scott Huish]

If you just need it displayed that way you could also use a custom number format of:
000\/00R00

 

[Rick Rothstein]

If you just need it displayed that way you could also use a custom number format of:
000\/00R00

I cannot believe I didn't think of that for my macro. Here is my macro revised to use the TEXT function to implement that format pattern...

[table="width: 500"]
[tr]
	[td]Sub AddSlashAndR()
  Dim Addr As String
  Addr = "D2:D" & Cells(Rows.Count, "D").End(xlUp).Row
  Range(Addr) = Evaluate("TEXT(" & Addr & ",""000\/00R00"")")
End Sub[/td]
[/tr]
[/table]

Note: Make sure to read Scott's suggestion in Message #5 in case that solution will serve your needs.

 

[kstate1988]

Thanks everyone for the fast response and the excellent suggestions!

These answers solved my question.

Nicely done.