Splitting address into multiple columns

Splitting address into multiple columns




Hi members,

I have about 30,000 rows of data in a single column.This column comprises of postal adress.The format of this address is not unique.I want each of these address rows to be split into the following columns(seperate columns)
Address line 1
Address line 2
City
State
Zipcode

The addresses look like these

170 S. 29th Street San Jose, CA 95116
1305 W. Belt Line Rd. Carrollton, TX 75006-6980
2260 Fe Mae Cruz Santa Clara, CA 95050
2485 W. Industrial Dr. Farmington Hills, MI 48335
2500 North America Drive West Seneca, NY 14224
5420 McConnell Ave. San Pedro, CA 90066
35451 Dumbarton Ct Newyork, CA 94560
3765 Quincy Street Hudsonville, MI 49426
12721 Hillcrest Rd., Suite 1011 Houston, TX 75230-2058
25800 Northwestern Hwy.,Suite 250 Southfield, MI 48075
150 W. Jefferson Ave., Ste. 2500 Detroit, MI 48226
14 East Averill Ave. Sparta, MI 49345
30987 Oregon Rd. Perrysburg, OH 43551
1646 Champagne Drive N. Lansing, MI 48604

So the output will look like in the attachment enclosed:



Is there a way I could split this into the columns.As I said these addresses are not in any specific format.

Thanks..Any help appreciated..

The kind of addressing you're looking at would require complex rules to parse it successfully. I'd suggest a utility like this one: http://www.cdxtech.com/Blog/post/How...ZipStream.aspx

Note: I've never used it, so you should investigate.

Here is a possible solution that may help you. See attached.

Now because it is not easy to decipher between street address and city in some cases, like the first in your sample, I created a range of possible street words, where we can make a break.. eg. street, ave, road, rd, hwy, etc...

Select this range and name it (enter name in Name box to the left of formula bar... I used Abbreviations). You can leave some blank cells in the range to fill in as you discover other abbreviations...

Then formula in B2:

Please Login or Register  to view this content.

in C2:

Please Login or Register  to view this content.

in D2:

Please Login or Register  to view this content.

in E2:

Please Login or Register  to view this content.

in F2:

Please Login or Register  to view this content.

each copied down.

NVBC -
Those are some seriously valiant formulas...KUDOS! My only concern is that depending on how "dirty" the data is, you might really end up having to add a lot to the abbreviations table in the J column. Things like Blvd vs Boulevard, St vs Suite vs Street, and what about compound addresses like Ave Court East? (like where I used to live ) Anyhow, cool. Hope it works out - looks promising.

First of all, Thanks a lot, I don't even have the knowledge to appreciate your smartness..That's the sad part !

My humble thanks for spending your time, effort and enthusiasm to get it to this.

I 'm attaching the spreadsheet where I did try to replicate your suggestions and formula.However, I am stuck for some reason..I dont understand ..

Can you PLs help?

Thanks THANKS and THANKS..

That is because you have 2 "street" words in your list.. there should only be one.. use the "Street" one.

If you are going to have street words that start in lower case in column A, then you can replace FIND function in B2 with SEARCH.

Also, my formulas take into account the period and/or commas after the street word.. so you should only need to list words with no periods or commas and just once... like (Rd St Ct Hwy etc...)

You are welcome..

You may need to modify some manually.. it's not 100% foolproof as thomas mentions...

Please also mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save