PDA

View Full Version : Solved: add slash in string



vzachin
10-18-2009, 09:43 AM
hi,

i have a column of data in which i need to add a slash to separate the alphas from the numerics.i don't need the slash at the beginning or end of the string

can you help me with a formula or better yet a vba solution?
please look at the attachment for examples


thanks
zach

Bob Phillips
10-18-2009, 11:18 AM
Public Sub ProcessData()
Dim NumberFlag As Boolean
Dim LastRow As Long
Dim LastChar As Long
Dim tmp As String
Dim i As Long, j As Long

With ActiveSheet

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

If .Cells(i, "C").Value <> "" Then

tmp = Mid(.Cells(i, "C").Value, 1, 1)
NumberFlag = IsNumeric(Mid(.Cells(i, "C").Value, 1, 1))
For j = 2 To Len(.Cells(i, "C").Value)

If NumberFlag <> IsNumeric(Mid(.Cells(i, "C").Value, j, 1)) Then

tmp = tmp & "/"
NumberFlag = Not NumberFlag
End If

tmp = tmp & Mid(.Cells(i, "C").Value, j, 1)
Next j
End If

.Cells(i, "C").Value = tmp
Next i
End With

End Sub

vzachin
10-18-2009, 12:34 PM
thanks bob!