On Sat, 8 Dec 2007 05:26:00 -0800, Mike H
wrote:

Hi,

I'm not entirely sure what you mean but if you want to do this
Col A Col B Col C
ABC123,AB234 ABC,AB 123234

then right click the sheet tab, view code paste this in and run it

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
Next
End Sub

It will extract a1 - a100 to columns B * C


Mike






Simpler, I think, to just replace the items that don't match.

e.g.:

re.Pattern = "[^A-Za-z]"
c.Offset(0, 1).Value = re.Replace(c.Value, "")
re.Pattern = "\D"
c.Offset(0, 2).Value = re.Replace(c.Value, "")


--ron