Extracting only the State from Full Address & Coordinate Cells

[dmshott]

I have a problem where I have a long list of addresses (400+) with the Street, City, State, Zip, Latitude, Longitude.

I need to extract only the state from each cell using a formula.

Here are some sample formats:

2001 Medical Parkway
Annapolis, Maryland 21401
(38.989104498000074, -76.53618365899996)

6767 West Greenfield Avenue
Milwaukee, Wisconsin 53214
(43.016701889000046, -87.99660976799998)

... But then there are also cells like this:

One Monarch Place
Springfield, MA 01144
(42.10150383600006, -72.59184032799999)

Is there any way to do this using a formula? My overall goal is to generate a State-map map, and count the number of instances for each state.

Any help would be HUGELY appreciated.

 

[ukmikeb]

Try :-

=LEFT(MID(A2,FIND(",",A2)+2,FIND("(",A2)-1-(FIND(",",A2)+2)),LEN(MID(A2,FIND(",",A2)+2,FIND("(",A2)-1-(FIND(",",A2)+2)))-6)

hth

 

[bosco_yip]

May be :

=LEFT(MID(A1,FIND(",",A1)+2,250),FIND(" ",MID(A1,FIND(",",A1)+2,250))-1)

 

[bosco_yip]

Or,

If the Zip always in 5 digits, the formula can be shortened to :

=MID(A1,FIND(",",A1)+2,FIND("(",A1)-FIND(",",A1)-9)