Solved: alpha-numeric parse

[vzachin]

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]

You should use regex. In case you're not familiar I arranged an old macro very similar to what you need.
[VBA]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[/VBA]

[vzachin]

Thanks ALe for your solution. The only problem i had was to figure out what characters to add in mytext.

zach

[ALe]

i have 2 requests

It sounded different.

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

[vzachin]

Hi Ale,

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

thanks for this coding.

zach