Hi Ray,

Am Wed, 8 Apr 2015 06:01:50 -0700 (PDT) schrieb Ray:

Thanks so much, works perfectly! Now I'll kill the rest of my morning dissecting the code to figure out how it works ... ;)

you are welcome.
You also can do it with Regular Expressions:

Sub Test()
Dim LRow As Long, i As Long
Dim varData1 As Variant, varData2 As Variant
Dim ptrn1 As String, ptrn2 As String
Dim re As Object

'Set a reference to Microsoft VBScript Regular Expressions
With ActiveSheet
LRow = .Cells(Rows.Count, "J").End(xlUp).Row
varData1 = .Range("J19:J" & LRow)
varData2 = .Range("J19:J" & LRow)

ptrn1 = "\d|\(|\)"
ptrn2 = "\D"

Set re = New RegExp
re.Pattern = ptrn1
re.IgnoreCase = False
re.Global = True

For i = 1 To UBound(varData1)
varData1(i, 1) = WorksheetFunction.Trim(re.Replace(varData1(i,
1), ""))
Next

re.Pattern = ptrn2
re.IgnoreCase = False
re.Global = True

For i = 1 To UBound(varData2)
varData2(i, 1) = WorksheetFunction.Trim(re.Replace(varData2(i,
1), ""))
Next

.Range("J19").Resize(UBound(varData1)) = varData1
.Range("K19").Resize(UBound(varData2)) = varData2
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional