Parsing information

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

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

If not, how to separate name from address ?

Cheers
--
AP

"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...o&userid=34702
> View this thread: http://www.excelforum.com/showthread...hreadid=544698
>

Same question about how to separate address from city

"Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de news:
%23Z7SYPnfGHA.4932@TK2MSFTNGP03.phx.gbl...
> According to your example, can we assume street address alwauys start with
> a number?
>
> If not, how to separate name from address ?
>
> Cheers
> --
> AP
>
> "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...o&userid=34702
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=544698
>>
>
>

Ardus,

Street address always starts with a number.

Dan

Address always begins with a number

Originally Posted by Ardus Petus

Same question about how to separate address from city

"Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de news:
%23Z7SYPnfGHA.4932@TK2MSFTNGP03.phx.gbl...
> According to your example, can we assume street address alwauys start with
> a number?
>
> If not, how to separate name from address ?
>
> Cheers
> --
> AP
>
> "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...o&userid=34702
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=544698
>>
>
>

I meant how to separate address (575 Manzanita Ave) from city (Chico)

--
AP

"dj1cincy" <dj1cincy.289lkd_1148394905.712@excelforum-nospam.com> a écrit
dans le message de news:
dj1cincy.289lkd_1148394905.712@excelforum-nospam.com...
>
> Address always begins with a number
>
> Ardus Petus Wrote:
>> Same question about how to separate address from city >>
>> "Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de
>> news:
>> %23Z7SYPnfGHA.4932@TK2MSFTNGP03.phx.gbl...
>> > According to your example, can we assume street address alwauys start
>> with
>> > a number?
>> >
>> > If not, how to separate name from address ?
>> >
>> > Cheers
>> > --
>> > AP
>> >
>> > "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...o&userid=34702
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=544698
>> >>
>> >
>> >
>
>
> --
> dj1cincy
> ------------------------------------------------------------------------
> dj1cincy's Profile:
> http://www.excelforum.com/member.php...o&userid=34702
> View this thread: http://www.excelforum.com/showthread...hreadid=544698
>

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

--
AP

"Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de news:
eMszOcnfGHA.4852@TK2MSFTNGP05.phx.gbl...
>I meant how to separate address (575 Manzanita Ave) from city (Chico)
>
> --
> AP
>
> "dj1cincy" <dj1cincy.289lkd_1148394905.712@excelforum-nospam.com> a écrit
> dans le message de news:
> dj1cincy.289lkd_1148394905.712@excelforum-nospam.com...
>>
>> Address always begins with a number
>>
>> Ardus Petus Wrote:
>>> Same question about how to separate address from city >>
>>> "Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de
>>> news:
>>> %23Z7SYPnfGHA.4932@TK2MSFTNGP03.phx.gbl...
>>> > According to your example, can we assume street address alwauys start
>>> with
>>> > a number?
>>> >
>>> > If not, how to separate name from address ?
>>> >
>>> > Cheers
>>> > --
>>> > AP
>>> >
>>> > "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...o&userid=34702
>>> >> View this thread:
>>> >> http://www.excelforum.com/showthread...hreadid=544698
>>> >>
>>> >
>>> >
>>
>>
>> --
>> dj1cincy
>> ------------------------------------------------------------------------
>> dj1cincy's Profile:
>> http://www.excelforum.com/member.php...o&userid=34702
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=544698
>>
>
>

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...o&userid=34702
> View this thread: http://www.excelforum.com/showthread...hreadid=544698
>

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

"Ardus Petus" wrote:

> I can presume it is the last word before the comma.
> Won't work if city = "Boca Raton"
>
> --
> AP
>
> "Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de news:
> eMszOcnfGHA.4852@TK2MSFTNGP05.phx.gbl...
> >I meant how to separate address (575 Manzanita Ave) from city (Chico)
> >
> > --
> > AP
> >
> > "dj1cincy" <dj1cincy.289lkd_1148394905.712@excelforum-nospam.com> a écrit
> > dans le message de news:
> > dj1cincy.289lkd_1148394905.712@excelforum-nospam.com...
> >>
> >> Address always begins with a number
> >>
> >> Ardus Petus Wrote:
> >>> Same question about how to separate address from city >>
> >>> "Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de
> >>> news:
> >>> %23Z7SYPnfGHA.4932@TK2MSFTNGP03.phx.gbl...
> >>> > According to your example, can we assume street address alwauys start
> >>> with
> >>> > a number?
> >>> >
> >>> > If not, how to separate name from address ?
> >>> >
> >>> > Cheers
> >>> > --
> >>> > AP
> >>> >
> >>> > "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...o&userid=34702
> >>> >> View this thread:
> >>> >> http://www.excelforum.com/showthread...hreadid=544698
> >>> >>
> >>> >
> >>> >
> >>
> >>
> >> --
> >> dj1cincy
> >> ------------------------------------------------------------------------
> >> dj1cincy's Profile:
> >> http://www.excelforum.com/member.php...o&userid=34702
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=544698
> >>
> >
> >
>
>
>

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
--
Sincerely, Michael Colvin


"dj1cincy" wrote:

>
> 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...o&userid=34702
> View this thread: http://www.excelforum.com/showthread...hreadid=544698
>
>