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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.