Split street address into cells

[sammy1]

Hello All. I need a few formulas that will split the below addresses into the following separate cells

1. House number
2. Street Name
3. City Name
4. State
5. Zip code
6. County

The data i receive looks like column A. I need a formula that will produce the right side of my table.
Thanks very much in advance
[TABLE="width: 1097"]
<tbody>[TR]
[TD]Source Data[/TD]
[TD][/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD]>>>[/TD]
[TD]House Number[/TD]
[TD]Street Name[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD]4321 Fielding Way Stone Mountain, GA, 30088 Dekalb County[/TD]
[TD]>>>[/TD]
[TD="align: right"]4321[/TD]
[TD]Field Way[/TD]
[TD]Stone Mountain[/TD]
[TD]GA[/TD]
[TD="align: right"]30088[/TD]
[TD]Dekalb County[/TD]
[/TR]
[TR]
[TD]123 Little River Dr Savannah, GA, 31419 Chatham County[/TD]
[TD]>>>[/TD]
[TD="align: right"]123[/TD]
[TD]Little River Dr[/TD]
[TD]Savannah[/TD]
[TD]GA[/TD]
[TD="align: right"]31419[/TD]
[TD]Chatham[/TD]
[/TR]
[TR]
[TD]40 Third St Elko, GA, 31025 Houston County[/TD]
[TD]>>>[/TD]
[TD="align: right"]40[/TD]
[TD]Third St[/TD]
[TD]Elko[/TD]
[TD]GA[/TD]
[TD="align: right"]31025[/TD]
[TD]Houston[/TD]
[/TR]
</tbody>[/TABLE]

 

[jarjarbingie]

separate fields can be done but it may be tough for the street name and city since there is nothing that can distinguish which is which unless we have a list of cities..

Hello All. I need a few formulas that will split the below addresses into the following separate cells

1. House number
2. Street Name
3. City Name
4. State
5. Zip code
6. County

The data i receive looks like column A. I need a formula that will produce the right side of my table.
Thanks very much in advance
[TABLE="width: 1097"]
<tbody>[TR]
[TD]Source Data[/TD]
[TD][/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[TD]Formula Needed[/TD]
[/TR]
[TR]
[TD]Address[/TD]
[TD]>>>[/TD]
[TD]House Number[/TD]
[TD]Street Name[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD]4321 Fielding Way Stone Mountain, GA, 30088 Dekalb County[/TD]
[TD]>>>[/TD]
[TD="align: right"]4321[/TD]
[TD]Field Way[/TD]
[TD]Stone Mountain[/TD]
[TD]GA[/TD]
[TD="align: right"]30088[/TD]
[TD]Dekalb County[/TD]
[/TR]
[TR]
[TD]123 Little River Dr Savannah, GA, 31419 Chatham County[/TD]
[TD]>>>[/TD]
[TD="align: right"]123[/TD]
[TD]Little River Dr[/TD]
[TD]Savannah[/TD]
[TD]GA[/TD]
[TD="align: right"]31419[/TD]
[TD]Chatham[/TD]
[/TR]
[TR]
[TD]40 Third St Elko, GA, 31025 Houston County[/TD]
[TD]>>>[/TD]
[TD="align: right"]40[/TD]
[TD]Third St[/TD]
[TD]Elko[/TD]
[TD]GA[/TD]
[TD="align: right"]31025[/TD]
[TD]Houston[/TD]
[/TR]
</tbody>[/TABLE]

 

[sammy1]

@ jarjarbingie
There is one way to distinguish the street name. However, i am not sure how to put this in excel formula format. If we look from the right hand side, we know there are two key distinguishing features of a street name.
1. From the right, a street name is Always preceded by a ONE WORD city which is preceded by state and a comma.
2. From the left, the street name is Always preceded by numbers.

 

[jarjarbingie]

in your example, Stone Mountain isn't one word..

@ jarjarbingie
There is one way to distinguish the street name. However, i am not sure how to put this in excel formula format. If we look from the right hand side, we know there are two key distinguishing features of a street name.
1. From the right, a street name is Always preceded by a ONE WORD city which is preceded by state and a comma.
2. From the left, the street name is Always preceded by numbers.

 

[azumi]

Maybe:

1. House Number
=LEFT(A2,SEARCH(" ",A2,1)-1)
2. Street Name
=SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(1-1)*LEN($A2)+1,LEN($A2))),C2,""),E2,"")
3. City
=TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(1-1)*LEN($A2)+1,LEN($A2))))," ",REPT(" ",60)),120))
4. State
=LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A2),",",REPT(" ",60)),120)),SEARCH(" ",TRIM(RIGHT(SUBSTITUTE(TRIM(A2),",",REPT(" ",60)),120)),1)-1)
5. ZIP
=LEFT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",90)),270)),SEARCH(" ",TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",90)),270)),1)-1)
6. County
=RIGHT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",90)),270)),LEN(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",90)),270)))-SEARCH(" ",TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",90)),270))))

or see this
https://www.dropbox.com/s/p62ajw1fl7r95c5/Split Address.xlsx?dl=0

hope this works, see u

 

[jarjarbingie]

B2 = left($a2,search(" ",$a2)-1)
c2 ?
D2 ?
E2 = mid($a2,search(",",$a2)+2,2)
f2 = mid($a2,search("/",substitute($a2,",","/",2))+2,5)
g2 = right($a2,len($a2)-search(f2,$a2)-5)

 

[sammy1]

Thanks Azumi, It looks good but on the 2nd and 3rd row Dr for drive and St for street are coming in as a part of the city. Any one have any tips on how to keep the city and street name completely separate.

Output from Azumi's formulas
[TABLE="class: aaa shownone, width: 925"]
<tbody>[TR="class: aai"]
[TD="class: aaj"]A1[/TD]
[TD="class: aac"][/TD]
[TD="class: xl66 aad, align: center"]House Number[/TD]
[TD="class: xl66 aak, align: center"]Street Name[/TD]
[TD="class: xl66 aal, align: center"]City[/TD]
[TD="class: xl66 aam, align: center"]State[/TD]
[TD="class: xl66 aan, align: center"]ZIP[/TD]
[TD="class: xl66 aao, align: center"]County[/TD]
[/TR]
[TR="class: aai"]
[TD="class: xl67"]4321 Fielding Way Stone Mountain, GA, 30088 Dekalb County[/TD]
[TD][/TD]
[TD="class: xl65 aap"]4321[/TD]
[TD="class: xl65 aaq"] Fielding Way[/TD]
[TD="class: xl65 aaq"]Stone Mountain[/TD]
[TD="class: xl65 aaq"]GA[/TD]
[TD="class: xl65 aaq"]30088[/TD]
[TD="class: xl65 aaq"]Dekalb County[/TD]
[/TR]
[TR="class: aai"]
[TD="class: xl67 aas"]123 Little River Dr Savannah, GA, 31419 Chatham County[/TD]
[TD][/TD]
[TD="class: xl65 aap"]123[/TD]
[TD="class: xl65 aaq"] Little River[/TD]
[TD="class: xl65 aaq"]Dr Savannah[/TD]
[TD="class: xl65 aaq"]GA[/TD]
[TD="class: xl65 aaq"]31419[/TD]
[TD="class: xl65 aaq"]Chatham County[/TD]
[/TR]
[TR="class: aai"]
[TD="class: xl67 aas"]40 Third St Elko, GA, 31025 Houston County[/TD]
[TD][/TD]
[TD="class: xl65 aap"]40[/TD]
[TD="class: xl65 aaq"] Third[/TD]
[TD="class: xl65 aaq"]St Elko[/TD]
[TD="class: xl65 aaq"]GA[/TD]
[TD="class: xl65 aaq"]31025[/TD]
[TD="class: xl65 aaq"]Houston County[/TD]
[/TR]
</tbody>[/TABLE]

 

[skywriter]

You are probably not going to find a formula that will work without something in the data to define for sure where the street address ends. The two word cities are going to be an issues as you have already seen. Something like the comma you see after city and state. Another possibility is if you can provide a list of all the street abbreviations.
St. Wy. Ct. Ave. Pl. etc, then you could have a code solution that splits up the data based on finding those abbreviations in your data. Maybe there's even a way to look at a list like that in a formula, but that's for the formula gurus on this board. I could probably handle a code solution with the abbreviations I mentioned.

Good Luck!!!

 

[sammy1]

I believe i can get a list of all cities in my data set. Is there a way we can find differentiate the street and city name equipped with this new information

Thanks very much for all the support.

 

[Rick Rothstein]

Another possibility is if you can provide a list of all the street abbreviations. St. Wy. Ct. Ave. Pl. etc, then you could have a code solution that splits up the data based on finding those abbreviations in your data.

Unfortunately, that list is long. Here is the US Post Office's official list..

C1 Street Suffix Abbreviations

Some of them I have never heard of, others I am sure could have a different spelling (for example, the Post Office shows only Ave for Avenue, but I have seen it written Av also).