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!
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!