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

Source Data		Formula Needed	Formula Needed	Formula Needed	Formula Needed	Formula Needed	Formula Needed
Address	>>>	House Number	Street Name	City	State	Zip	County
4321 Fielding Way Stone Mountain, GA, 30088 Dekalb County	>>>	4321	Field Way	Stone Mountain	GA	30088	Dekalb County
123 Little River Dr Savannah, GA, 31419 Chatham County	>>>	123	Little River Dr	Savannah	GA	31419	Chatham
40 Third St Elko, GA, 31025 Houston County	>>>	40	Third St	Elko	GA	31025	Houston

<tbody>
</tbody>

 

[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

Source Data		Formula Needed	Formula Needed	Formula Needed	Formula Needed	Formula Needed	Formula Needed
Address	>>>	House Number	Street Name	City	State	Zip	County
4321 Fielding Way Stone Mountain, GA, 30088 Dekalb County	>>>	4321	Field Way	Stone Mountain	GA	30088	Dekalb County
123 Little River Dr Savannah, GA, 31419 Chatham County	>>>	123	Little River Dr	Savannah	GA	31419	Chatham
40 Third St Elko, GA, 31025 Houston County	>>>	40	Third St	Elko	GA	31025	Houston

<tbody>
</tbody>

 

[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

A1		House Number	Street Name	City	State	ZIP	County
4321 Fielding Way Stone Mountain, GA, 30088 Dekalb County		4321	Fielding Way	Stone Mountain	GA	30088	Dekalb County
123 Little River Dr Savannah, GA, 31419 Chatham County		123	Little River	Dr Savannah	GA	31419	Chatham County
40 Third St Elko, GA, 31025 Houston County		40	Third	St Elko	GA	31025	Houston County

<tbody>
</tbody>

 

[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).