Re: Parsing information

Giganews Newsgroups
Subject: Re: Parsing information
Posted by:  Ardus Petus (ardus.pet…@laposte.net)
Date: Tue, 23 May 2006

Here is a solution.
It needs a reference to Microsoft VBScript Regular Expressions 5.5

HTH
--
AP

'--------------------------------------
Sub splitAddress()
    Dim rCell As Range
    Dim re As RegExp
    Dim mc As MatchCollection
    Dim m As Match
    Dim o As Long
    Set re = New RegExp
    re.IgnoreCase = True
    re.Pattern = "^([a-z ]+)(\d+ [a-z ]+[^,]) ([a-z]+), (\D+) ([\d-]+)
([\d-]+)"

    For Each rCell In Range( _
        Range("A1"), _
        Cells(Rows.Count, "A").End(xlUp))
        With rCell
            Set mc = re.Execute(rCell.Text)
            If mc.Count = 1 Then
                Set m = mc(0)
                If m.SubMatches.Count = 6 Then
                    For o = 0 To 5
                        .Offset(0, o + 1) = Trim(m.SubMatches(o))
                    Next o
                End If
            End If
        End With
    Next rCell
End Sub
'----------------------------------
"dj1cincy" <dj1cincy.289kem_1148393479.715@excelforum-nospam.com> a écrit
dans le message de news:
dj1cincy.289kem_1148393479.715@excelforum-nospam.com...
>
> Is there a way to take a list of names and addresses such as
>
> Chico Nissan Hyundai 575 Manzanita Ave Chico, CA 95926-1322
> 530-891-1777
> Chino Hills Ford 4480 Chino Hills Pkwy Chino, CA 91710-5602
> 909-393-9331
>
> and create a macro to be able to put the name in one field, street
> addresss in another, city in another, state in another, zip in another
> , and phone number in another?
>
> Any help would be appreciated
>
> Dan
>
> --
> dj1cincy
> ------------------------------------------------------------------------
> dj1cincy's Profile:
>http://www.excelforum.com/member.php?action=getinfo&userid=34702
> View this thread:http://www.excelforum.com/showthread.php?threadid=544698

Replies

None

In response to

Parsing information posted by dj1cincy on Tue, 23 May 2006