PDA

View Full Version : Solved: inserting a / between alpha and numeric



vzachin
09-14-2008, 06:39 PM
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
09-15-2008, 12:28 AM
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
09-15-2008, 12:39 AM
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
09-15-2008, 12:41 AM
Why would you run it again?

shamsam1
09-15-2008, 12:43 AM
wel if more values are inserted after running macro once..

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

Bob Phillips
09-15-2008, 01:32 AM
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
09-15-2008, 01:37 AM
thanks bob...

vzachin
09-15-2008, 03:49 AM
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
09-15-2008, 03:56 AM
hi Bob,

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

zach