Help Parsing Text into Separate Fields

[akuney]

Hello,

I could really use some help parsing this data. All of the data is in the same field, and I would like to have each address field (i.e. Address1, City, State, Zip Code) in separate fields.

100 Laketrail Drive , Alpharetta, Georgia, 30022
1001 St Charles Ave , Atlanta, Georgia, 30306
102 Meadow Court , Fairburn, Georgia, 30213

I've found how I can parse out "100 Laketrail Drive" (for example):
=LEFT(A2,FIND(",",A2)-2)

And I've found how I can parse out "30022":
=TRIM(RIGHT($A$2,LEN($A$2)-FIND("^^",SUBSTITUTE($A$2,",","^^",LEN($A$2)-LEN(SUBSTITUTE($A$2,",",""))))))

But I need help with parsing out "Alpharetta" and "Georgia", please! I think I should be able to count the number of commas and parse out the data from one comma to the next comma. But I've spent a LONG time figuring it out and I can't seem to get it.

Can someone help me, please!

Aaron

 

[pgc01]

Hi Aaron
Welcome to the board

If the addresses have, like in the examples, the fields separated by commas, you just have to select the column and use Data->Text to Columns choosing comma for delimiter.

 

[akuney]

Wow, don't I feel silly. I've seen that screen when importing new data, but I didn't realize I could do that from within a worksheet.

Thanks!