| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Entering information from 1 worksheet to another, if criteria is m | Excel Discussion (Misc queries) | |||
| Importing information from 2 workbooks into a 3rd one | Links and Linking in Excel | |||
| Importing information from 2 workbooks into a 3rd one | Excel Worksheet Functions | |||
| Importing information from 2 workbooks into a 3rd one | Excel Discussion (Misc queries) | |||
| Switching Information | Excel Discussion (Misc queries) | |||
Hybrid Mode
