|
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 -- dj1cincy ------------------------------------------------------------------------ dj1cincy's Profile: http://www.excelforum.com/member.php...o&userid=34702 View this thread: http://www.excelforum.com/showthread...hreadid=544698 |
Parsing information
Same question about how to separate address from city
"Ardus Petus" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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 |
Parsing information
Ardus, Street address always starts with a number. Dan -- dj1cincy ------------------------------------------------------------------------ dj1cincy's Profile: http://www.excelforum.com/member.php...o&userid=34702 View this thread: http://www.excelforum.com/showthread...hreadid=544698 |
Parsing information
Address always begins with a number Ardus Petus Wrote: Same question about how to separate address from city "Ardus Petus" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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 |
Parsing information
I meant how to separate address (575 Manzanita Ave) from city (Chico)
-- AP "dj1cincy" a écrit dans le message de news: ... Address always begins with a number Ardus Petus Wrote: Same question about how to separate address from city "Ardus Petus" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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 |
Parsing information
I can presume it is the last word before the comma.
Won't work if city = "Boca Raton" -- AP "Ardus Petus" a écrit dans le message de news: ... I meant how to separate address (575 Manzanita Ave) from city (Chico) -- AP "dj1cincy" a écrit dans le message de news: ... Address always begins with a number Ardus Petus Wrote: Same question about how to separate address from city "Ardus Petus" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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 |
Parsing information
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" a écrit dans le message de news: ... 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 |
Parsing information
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" a écrit dans le message de news: ... I meant how to separate address (575 Manzanita Ave) from city (Chico) -- AP "dj1cincy" a écrit dans le message de news: ... Address always begins with a number Ardus Petus Wrote: Same question about how to separate address from city "Ardus Petus" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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 |
Parsing information
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 |
| All times are GMT +1. The time now is 08:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com