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]

Yes, I've run into some of these problems that you mentioned.

I will inform the chapter that they need to fix their data based upon the USPS format.

Thank you again,

Michael

 

[István Hirsch]

I would download the list of cities in California along with their zip codes (Sheet 2), try to find the city according to the found zip code, and would check if the city is really contained in Sheet 1 Column A. If there are many differences I would try another (up-to-date) zip code list.

Excel Workbook
	A	B	C	D
1	1271 ISLAND DRIVE ALAMEDA, CA 94502	Alameda
2	14723 BANCROFT AVENUE SAN LEANDRO, CA 94578	San Leandro
3	560 THARP DRIVE MORAGA, CA 94556	Moraga
4	5002 LADNER STREET FREMONT, CA 94538	Fremont
5	21820 GILL PORT LANE WALNUT CREEK, CA 94598	Walnut Creek
6	710A COOLIDGE AVE. OAKLAND, CA 94602	Oakland
7	26765 VENTRY WAY SAN LORENZO, CA 94580	San Lorenzo
8	2068 WILSON AVENUE CASTRO VALLEY, CA 94546	Castro Valley
9	13970 INTERNATIONAL BLVD. #22 1 OAKLAND, CA 94603	Oakland
10	4567 FAIRWAY AVENUE OAKLAND, CA 94605	Oakland
11	20150 NATALIE COURT CASTRO VALLEY, CA 94546	Castro Valley
12	1750 POMONA AVENUE EL CERRITO, CA 93704	Fresno		#VALUE!
13	975 WESTWOOD COURT RICHMOND, CA 94803	El Sobrante		#VALUE!
14	38952 DUBLIN GREEN DRIVE DUBLIN, CA 94568, CA 94596	Walnut Creek		#VALUE!
15	200 VIOLET STREET SAN LEANDRO, CA 94578	San Leandro
16	963 SANTIAGO DRIVE DANVILLE, CA 94526	Danville
17	4516 MLK JR. WAY #2C BERKELEY, CA 94709	Berkeley
18	26783 SIERRA AVE HAYWARD, CA 94541	Hayward
19	403 VINEYARD COURT PLEASANT HILL, CA 94523	Pleasant Hill
20	876538 Tidewater Drive Union City, CA 94587	Union City
21	834A BEACON STREET SAN FRANCISCO, CA 94134	San Francisco
22	897 REFLECTIONS DRIVE #22 SAN RAMON, CA 94583	San Ramon
Sheet1

Excel Workbook
	A	B
1	90001	Los Angeles
2	90002	Los Angeles
3	90003	Los Angeles
4	90004	Los Angeles
5	90005	Los Angeles
6	90006	Los Angeles
7	90007	Los Angeles
8	90008	Los Angeles
9	90009	Los Angeles
Sheet2

 

[bearcub]

This is cool, thank you. What do the exclamation marks mean in the second formula (what kind of wild card do they represent)?

Michael

 

[István Hirsch]

Exclamation mark was to show items where the name of the city belonging to the given zip code and the one in the address string is different. Now these issues are marked by the #VALUE error in Sheet 1 column D. These #VALUE erros should be checked why the city in the string as well as the city in the "official" list is different.

If there are cities, for example named XYZ and North XYZ then the zip code list in Sheet 2 should be first put in descending order of the length of the city name before use.

 

[István Hirsch]

It seems in Sheet 1 cell D1 this formula is better:

=IF(SEARCH(B1,A1)>0,"")