Splitting an address field into separate fields

[bearcub]

I following the following address field that I received from one of our field offices. They combined the address and CSZ into one field. However, I need to have them all in a separate field for my formulas to work properly. I can't use formulas because until now, all of the fields have been separated properly or the address fields are missing.

This is the type of field format I just received.

14625 BANCROFT AVENUE SAN LEANDRO, CA 94578


looks like their is a comma delimited for the city but everything else has spaces . This data comes from their own separate db.

is it possible to have it appear in the following format:

Col D                             Col E              Col F     Col G
14625 BANCROFT AVENUE       San Leandro,              CA        94578

Thank you for your help,

The sheet I'm working on now has close to 7 hundred hours. Other offices might have more or have less.

Michael

 

[bearcub]

Oh, forgot to mention, all this data was originally included all in Column D. I would like to leave the address in Col D but move the City State and Zip into those other columns.

Thank you

Michael

 

[Rick Rothstein]

I following the following address field that I received from one of our field offices. They combined the address and CSZ into one field. However, I need to have them all in a separate field for my formulas to work properly. I can't use formulas because until now, all of the fields have been separated properly or the address fields are missing.

This is the type of field format I just received.

14625 BANCROFT AVENUE SAN LEANDRO, CA 94578


looks like their is a comma delimited for the city but everything else has spaces . This data comes from their own separate db.

is it possible to have it appear in the following format:

Col D                             Col E              Col F     Col G
14625 BANCROFT AVENUE       San Leandro,              CA        94578

Thank you for your help,

The sheet I'm working on now has close to 7 hundred hours. Other offices might have more or have less.

My advice would be to have the field office output the address with a comma between the street name and the city. The change should be simple for them to implement and their doing so would save you lots of headaches. The only way to parse what they are now sending you is to create a dictionary of all the possible street type designations (Avenue, Street, Court, Lane, etc.) and all of their possible abbreviations so that you have some way of telling where the city name begins. And even then, you will still have addresses that will fail to parse correctly as not all street addresses end with a street type designation which means you would still have to inspect every address to make sure it was not one of those exceptions. So to repeat... get the field office to modify their output.

 

[bearcub]

I understand. So what you're saying is that it would be difficult to really create a procedure to split the data. I have thought about using formulas or even flash fill but it would be a nightmare to delete the CSZ portion from the cell. Thank you for the advice

Michael

 

[Peter_SSs]

As another (Australian) example of the problems Rick has mentioned. In Australia there is a town called Lawrence and a town called St Lawrence.

Consider the address "24 Claire Circuit St Lawrence"
It could be in Clair Circuit St in the town Lawrence or it could be in Clair Circuit in the town St Lawrence.
There is no logical way to decide.

 

[István Hirsch]

Could you give us some more data to process?

 

[bearcub]

When I get to work I'll submit the list of cities I have. It is possible that their many be consistent format that would be able to be translated into VBA.

Thank you for your help.

 

[bearcub]

Here is a list for some of the data coming out of their system. Looking at the data I do see consistent delimiiters when comes to the comma, which is right after the city before CA and the zip code.

I'd be curious to know if there was either a macro or a formula that could split this into the appropriate columns.

Thank you for your help,

Michael

1271 ISLAND DRIVE ALAMEDA, CA  94502
14723 BANCROFT AVENUE SAN LEANDRO, CA  94578
560 THARP DRIVE MORAGA, CA  94556
5002 LADNER STREET FREMONT, CA  94538
21820 GILL PORT LANE WALNUT CREEK, CA  94598
710A COOLIDGE AVE. OAKLAND, CA  94602
26765 VENTRY WAY SAN LORENZO, CA  94580
2068 WILSON AVENUE CASTRO VALLEY, CA  94546
13970 INTERNATIONAL BLVD. [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=22]#22 1[/URL]  OAKLAND, CA  94603
4567 FAIRWAY AVENUE OAKLAND, CA  94605
20150 NATALIE COURT CASTRO VALLEY, CA  94546
1750 POMONA AVENUE EL CERRITO, CA  93704
975 WESTWOOD COURT RICHMOND, CA  94803
38952 DUBLIN GREEN DRIVE DUBLIN, CA 94568, CA  94596
200 VIOLET STREET SAN LEANDRO, CA  94578
963 SANTIAGO DRIVE DANVILLE, CA  94526
4516 MLK JR. WAY #2C  BERKELEY, CA  94709
26783 SIERRA AVE HAYWARD, CA  94541
403 VINEYARD COURT PLEASANT HILL, CA  94523
876538 Tidewater Drive Union City, CA  94587
834A BEACON STREET SAN FRANCISCO, CA  94134
897 REFLECTIONS DRIVE #22  SAN RAMON, CA  94583

 

[bearcub]

The Dubliin Green street is actually how it came out of the data base. This would definitely need to be fixed on their end. It isn't a typo on my part.

 

[jtakw]

Hi,

As mentioned by Rick in Post #3 and Peter in Post #5 , your problem is at least two fold, between the "Streets" (Ave. Avenue, Boulevard Blvd., Road Rd., Court Ct., Main St. North, etc.) and the "Cities" having 1 word or 2 or maybe even 3 (Oakland, San Francisco, San Luis Obispo, etc.); you'll need to at least build 2 sets of databases to identify these, then even after that there's still the possibility of mismatches, for example, there's a Oakland Ave. in the city of Oakland.

So the most logical answer for you is to have the source of this data put in delimiters for the Streets and the Cities. And Possibly, even the street numbers, cause as in one of your samples above: 834A Beacon St., what if it was 834 A Beason St., the formula wouldn't know if the "A" is part of the street number or the Street name.