Extract data from string

[westend9876]

I have a list of addresses in column D that appear in the following two formats:

GIG HARBOR, WA 98335
SEATTLE, WA98335

I need to extract the two letter state abbreviation, which in the example would be WA. However, sometimes there is a space between the state and 5 digit zip and other times there isn't.

 

[Marc L]

sometimes there is a space between the state and 5 digit zip
and other times there isn't.

Which no involves within your need !

Formula : =MID(A1,SEARCH(", ",A1)+2,2) …
​

 

[westend9876]

Which no involves within your need !

Formula : =MID(A1,SEARCH(", ",A1)+2,2) …
​

This formula did not work. It returns #VALUE!

 

[PaulF]

Just change the A to a D in Marc's formula:

=MID(D16,SEARCH(", ",D16)+2,2)

 

[bosco_yip]

Or……….

=TRIM(MID(D1,FIND(",",D1)+2,LEN(D1)-FIND(",",D1)-6))

Regards