How do I extract upper case out of a text

[mjmoon]

My data is in one cell and is NOT consistant. Here is an example of the data:
Collateral: Real Estate - Commercial - Without Dwelling - Brimark - 3200 W 6th St, Small Town, SD.

I want to extract the state out of each cell, so in this example I would want my result in a seperate state to be SD. Sometimes the data has the state and sometimes it doesn't, sometimes the state is in the middle or at the end. Like I said it is not consistant.

Any suggestions on a function that I could use?

 

[danzon]

is there always a period after the state initials like your sample data shows ? if so, can there be periods elsewhere in the data ?

 

[mjmoon]

No there is no periods after state abbreviation in any of my data. I put that in there out of habit since it was an end of the sentence, my error, sorry.

 

[millhouse123]

Is the state always SD? If it is you can use the following formula
=Mid(A1,FIND("sd",A1),2) where A1 is the cell with you data.

 

[mjmoon]

No, there are multiple states but I could possible use your formula on the states most commonly used which may help me trim my data down somewhat.

 

[Snaps]

If there is always a comma before the state you could use Text to Columns to separate it out.

 

[danzon]

Try this ARRAY ( CTRL > SHIFT > ENTER )

{=INDEX($Z$1:$Z$50,MATCH(TRUE,ISNUMBER(FIND(" "&$Z$1:$Z$50&" "," "&A1&" ")),0))}

where A1 has your string

and E1 thru E50 contains the list of 50 state abbreviations in all caps

will only work if the 2 letter state is always isolated... space directly before and after

 

[mjmoon]

Millhouse123 thank you. By using your formula I was able to get my data trimmed down to a level where I can mannually work on it.

 

[mjmoon]

DANZON - I like your solution, very creative I would have never thought of that one.

Thank you everybody for you time and comments