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]

 

[Rick Rothstein]

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

If you had such a list, then the space in front of them could be physically changed to a comma/space giving us a delimiter which other code could then use to split apart each address cell.