thegreals
asked on
Excel Macro to split address
Hi Gurus,
I have a list of thousands of addresses that I need to import into another program but need the address split into:
Street Number (in Column B) Street Name (in Column C) Street Type (in Column C)
Here is the sample data in Column A:
33 Darley Road
1 Huntly Road
1 Lakeview Parade
1 Melbourne Avenue
1/1 Huntly Rd
1/10 Warrigal Jones Street
1/105 The Esplanade
1/107 The Esplanade
1/121 The Esplanade
1/12-14 Barrenjoey Rd
1/125 Springwood Street
1/13 Lagoon Street
1/13 Webb Road
1/14 Augusta Street
1/16 Barrenjoey Road
1/17 Greene Street
1/18 Warwick Street
1/188 West Street
The last string will always be street type. Happy to do this by pastin
Thanks gurus
Regards,
the greals
I have a list of thousands of addresses that I need to import into another program but need the address split into:
Street Number (in Column B) Street Name (in Column C) Street Type (in Column C)
Here is the sample data in Column A:
33 Darley Road
1 Huntly Road
1 Lakeview Parade
1 Melbourne Avenue
1/1 Huntly Rd
1/10 Warrigal Jones Street
1/105 The Esplanade
1/107 The Esplanade
1/121 The Esplanade
1/12-14 Barrenjoey Rd
1/125 Springwood Street
1/13 Lagoon Street
1/13 Webb Road
1/14 Augusta Street
1/16 Barrenjoey Road
1/17 Greene Street
1/18 Warwick Street
1/188 West Street
The last string will always be street type. Happy to do this by pastin
Thanks gurus
Regards,
the greals
Rory Archibald
If that's your actual data, then Data-text to columns, choose Delimited, then choose Space as the delimiter.
Some of the streets have two names :)
Its a race for the space string function/vba. I'm just going to watch.
Its a race for the space string function/vba. I'm just going to watch.
Ah, I missed that!
Just for the fun of it:
What would the expected Street Type be for
1/105 The Esplanade
??
or
12 Harlan Grove
13 Eternal Close
??
What would the expected Street Type be for
1/105 The Esplanade
??
or
12 Harlan Grove
13 Eternal Close
??
> 12 Harlan Grove
ha!. very cute.
<my last off topic comment.>
fzzzz
ha!. very cute.
<my last off topic comment.>
fzzzz
If there is always a space between the number and name then this should work
Use this formula in Col B for the number
=LEFT(A1,SEARCH(" ",A1))
Use this formula in Col C for the name
=RIGHT(A1,LEN(A1)-SEARCH("
Use this formula in Col B for the number
=LEFT(A1,SEARCH(" ",A1))
Use this formula in Col C for the name
=RIGHT(A1,LEN(A1)-SEARCH("
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops! I missed the bit about street type in col D.
Nice article Patrick
Nice article Patrick
@JohnHamling
Thanks for that feedback!
If you haven't done so already, I'd appreciate it if you'd click Yes on the article page for "was this helpful?"
:)
Thanks for that feedback!
If you haven't done so already, I'd appreciate it if you'd click Yes on the article page for "was this helpful?"
:)
ASKER
Hey Patrick,
Man....that is awesome..I love how I can add street names into the equation - genuis...genuis...genius.
Regards,
the greals
Man....that is awesome..I love how I can add street names into the equation - genuis...genuis...genius.
Regards,
the greals