How to pick out consecutive capital letters from a cell?

[novice_excel_user]

Hello All,

This is my first of likely many questions on this forum, as my excel skills are far too basic for my liking and I am keen to better them.

I am given a task where I have a row of various different addresses for head offices of public companies. I just need the US state of where they are based, but each address is typed differently so I have no way of knowing how to have the state picked out.

I do know that in most/all my cells, the state is two consecutive capital letters, and nothing has consecutive capital letters in the address. So is there a way I can create a formula where the referenced cell can pick out anything that has consecutive capital letters?

An example of two addresses in my Cell (R7) are:

3050 Bowers Avenue PO Box 58039 Santa Clara, CA 95052-8039 United States.

<tbody>
</tbody>

and

1320 Ridder Park Drive San Jose, CA 95131 United States.

In these cases, I have different sized addresses in length, but CA is the only characters which are consecutively upper case. So if a formula were to pick up anything with 2 consecutive upper case characters, then I'd pick the state out of all my addresses.

Just to say once again, the addresses above are for publically listed companies, so sensitive information here.


Thanks for whoever knows how to do this!

 

[Scott T]

If the address are like your example with only one comma before the state then this should work.

=TRIM(MID(A3,SEARCH(",",A3)+1,3))

 

[FormR]

Hi, welcome to the forum!

and nothing has consecutive capital letters in the address.

Are you sure about that

An example of two addresses in my Cell (R7) are:

3050 Bowers Avenue PO Box 58039 Santa Clara, CA 95052-8039 United States.

<tbody>
</tbody>

If your addresses are not as well structured as the two examples then here is another option you can try:

1st -define a name via the "name manager" called states that refers to:

={" AL "," AK "," AZ "," AR "," CA "," CO "," CT "," DE "," FL "," GA "," HI "," ID "," IL "," IN "," IA "," KS "," KY "," LA "," ME "," MD "," MA "," MI "," MN "," MS "," MO "," MT "," NE "," NV "," NH "," NJ "," NM "," NY "," NC "," ND "," OH "," OK "," OR "," PA "," RI "," SC "," SD "," TN "," TX "," UT "," VT "," VA "," WA "," WV "," WI "," WY "}

And then try the following formula:

Excel 2013/2016
	A	B
1	3050 Bowers Avenue PO Box 58039 Santa Clara, CA 95052-8039 United States.	CA
2	1320 Ridder Park Drive San Jose, CA 95131 United States.	CA
3	An Address in FL without a comma	FL
TI-CT

Cell Formulas
Range	Formula
B1	=MID(A1,AGGREGATE(15,6,FIND(States," "&A1&" "),1),2)

Named Ranges
Name	Refers To	Cells
States	={" AL "," AK "," AZ "," AR "," CA "," CO "," CT "," DE "," FL "," GA "," HI "," ID "," IL "," IN "," IA "," KS "," KY "," LA "," ME "," MD "," MA "," MI "," MN "," MS "," MO "," MT "," NE "," NV "," NH "," NJ "," NM "," NY "," NC "," ND "," OH "," OK "," OR "," PA "," RI "," SC "," SD "," TN "," TX "," UT "," VT "," VA "," WA "," WV "," WI "," WY "}

 

[novice_excel_user]

Thank you @MrExcelMVP! Haha I should have said most consecutive upper case letters will be states.

 

[novice_excel_user]

Thank you very much @ScottT. This actually cleaned up most of my addresses! Forgot there was a cheeky ", " before most of the states.