PDA

View Full Version : Solved: alpha-numeric parse



vzachin
01-11-2008, 09:18 AM
hi,

i have 2 requests

in column B, i have data that is maximum 6 characters in length.i need to parse any cell that contains alpha-numeric or numeric-alpha plus rogue characters such as /,*, ,(comma)..etc,
and place the cell contents from column a & b into column e & f.

and then in column g, i want to show the column f data without the rogue characters.

is this possible?

thanks
zachi

ALe
01-11-2008, 10:02 AM
You should use regex. In case you're not familiar I arranged an old macro very similar to what you need.
Sub doit()
Dim mr As Range
Dim mycell As Range
Dim RgRif As Range
Dim mytext As String, newtext As String
Dim i As Integer, j As Integer
mytext = "*/-;,."
Set mr = Range("a1:" & Range("a6500").End(xlUp).Address)
For Each mycell In mr
If Not IsNumeric(mycell.Offset(0, 1).Value) Then
For i = 1 To Len(mytext)
If InStr(1, mycell.Offset(0, 1), Mid(mytext, i, 1)) > 0 Then
Set RgRif = Range("e65000").End(xlUp).Offset(1, 0)
RgRif.Value = mycell.Value
RgRif.Offset(0, 1).Value = mycell.Offset(0, 1).Value

For j = 1 To Len(mytext)
On Error Resume Next
newtext = Replace(RgRif.Offset(0, 1).Value, Mid(mytext, i, 1), "")
On Error GoTo 0
RgRif.Offset(0, 2).Value = newtext
Next j
Exit For
End If

Next i
End If
Next mycell
End Sub

vzachin
01-16-2008, 06:21 AM
Thanks ALe for your solution. The only problem i had was to figure out what characters to add in mytext.

zach

ALe
01-16-2008, 10:30 AM
i have 2 requests
It sounded different.

run this to see al chr codes
Sub CHARCODE()
For i = 33 To 255
Cells(i - 32, 1).Value = Chr(i)
Next i
End Sub

vzachin
01-18-2008, 10:35 AM
Hi Ale,

and here i was hunting & pecking the keyboard for all the characters...:roller:

thanks for this coding.

zach