Parsing information

D

dj1cincy

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
 
A

Ardus Petus

According to your example, can we assume street address alwauys start with a
number?

If not, how to separate name from address ?

Cheers
 
A

Ardus Petus

I can presume it is the last word before the comma.
Won't work if city = "Boca Raton"
 
A

Ardus Petus

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" <[email protected]> a écrit
dans le message de [email protected]...
 
S

Sheila D

You could try the Text to Columns command on the Data menu... Might split it
too much though
Sheila
 
M

Michael

dj1cicy, like Shiela D suggested you could use Date-Text to Columns with
space as the separator. Next, you could use helper columns with the
Concatenate function to recombine the auto dealers name and the street
address. HTH
 
Top