How to pick out consecutive capital letters from a cell?

novice_excel_user

New Member
Joined
Sep 25, 2017
Messages
3
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:

[TABLE="width: 643"]
<tbody>[TR]
[TD]3050 Bowers Avenue PO Box 58039 Santa Clara, CA 95052-8039 United States.[/TD]
[/TR]
</tbody>[/TABLE]

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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
If the address are like your example with only one comma before the state then this should work.
Code:
=TRIM(MID(A3,SEARCH(",",A3)+1,3))
 
Upvote 0
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:
[TABLE="width: 643"]
<tbody>[TR]
[TD]3050 Bowers Avenue PO Box 58039 Santa Clara, CA 95052-8039 United States.[/TD]
[/TR]
</tbody>[/TABLE]

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:

Code:
={" 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
AB
13050 Bowers Avenue PO Box 58039 Santa Clara, CA 95052-8039 United States.CA
21320 Ridder Park Drive San Jose, CA 95131 United States.CA
3An Address in FL without a commaFL
TI-CT
Cell Formulas
RangeFormula
B1=MID(A1,AGGREGATE(15,6,FIND(States," "&A1&" "),1),2)
Named Ranges
NameRefers ToCells
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 "}
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top