Solved: inserting a / between alpha and numeric

[vzachin]

hi,

in column A5, i have a string about 20 characters which are alpha & numerics.
i need to insert a "/" (slash) between the alphas & numerics.
if the string begins with an alpha, then i need a slash at the beginning.
i do not need a slash at the end of the string.

i don't know where to begin. is this possible to accomplish?

thanks
zach

[Bob Phillips]

A bit brute force



Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

For j = Len(.Cells(i, "A").Value) To 2 Step -1

If (IsNumeric(Mid$(.Cells(i, "A").Value, j, 1)) And Not IsNumeric(Mid$(.Cells(i, "A").Value, j - 1, 1))) Or _
(Not IsNumeric(Mid$(.Cells(i, "A").Value, j, 1)) And IsNumeric(Mid$(.Cells(i, "A").Value, j - 1, 1))) Then

.Cells(i, "A").Value = Left$(.Cells(i, "A").Value, j - 1) & "/" & _
Right$(.Cells(i, "A").Value, Len(.Cells(i, "A").Value) - j + 1)
End If
Next j
Next i

End With

Application.ScreenUpdating = True

End Sub

[shamsam1]

hi bob

this code work perfect but when macro runs second time or more ,that many slash are inserted.
how to avoid that

[Bob Phillips]

Why would you run it again?

[shamsam1]

wel if more values are inserted after running macro once..

just curios to know..
macro should over look already inserted slash

[Bob Phillips]

Just clear them out first then




Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

.Cells(i, "A").Value = Replace(.Cells(i, "A").Value, "/", "")
For j = Len(.Cells(i, "A").Value) To 2 Step -1

If (IsNumeric(Mid$(.Cells(i, "A").Value, j, 1)) And Not IsNumeric(Mid$(.Cells(i, "A").Value, j - 1, 1))) Or _
(Not IsNumeric(Mid$(.Cells(i, "A").Value, j, 1)) And IsNumeric(Mid$(.Cells(i, "A").Value, j - 1, 1))) Then

.Cells(i, "A").Value = Left$(.Cells(i, "A").Value, j - 1) & "/" & _
Right$(.Cells(i, "A").Value, Len(.Cells(i, "A").Value) - j + 1)
End If
Next j
Next i
End With

Application.ScreenUpdating = True

End Sub

[shamsam1]

thanks bob...

[vzachin]

hi Bob,

brute force is good!
just one last question, how would i add "/" to the beginning of the string if the 1st character is an alpha?

thanks
zach

[vzachin]

hi Bob,

never mind. was able to figure it out from your coding.
thanks again!

zach