Extract State Abbreviation with No Commas - HELP!

[drockdamian]

Hello All,

I need help extracting the state abbreviation and zip code from this single string address line. There are two space in between the state abbreviation and the zip code; only one space between the end of zip code and country. Here is a sample.

8270 Petaluma Hill Rd Attn: Broc Croll Penngrove CA 94951-9747 USA

1105 Arden Rd Pasadena CA 91106-4005 USA

2010 Wentworth Dr Billings MT 59105-3535 USA

3372 S 7780 W Magna UT 84044-2041 USA

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is what you want.

21 12 14.xlsm
	A	B	C
1		State	Zip
2	8270 Petaluma Hill Rd Attn: Broc Croll Penngrove CA 94951-9747 USA	CA	94951-9747
3	1105 Arden Rd Pasadena CA 91106-4005 USA	CA	91106-4005
4	2010 Wentworth Dr Billings MT 59105-3535 USA	MT	59105-3535
5	3372 S 7780 W Magna UT 84044-2041 USA	UT	84044-2041
State and Zip

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),400),100))
C2:C5	C2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),200),100))

 

[drockdamian]

Wow! It worked, thank you so much! My formula was definitely not set it up like that. I'll have to study what each means. Thanks again Peter.

Welcome to the MrExcel board!

See if this is what you want.

21 12 14.xlsm
	A	B	C
1		State	Zip
2	8270 Petaluma Hill Rd Attn: Broc Croll Penngrove CA 94951-9747 USA	CA	94951-9747
3	1105 Arden Rd Pasadena CA 91106-4005 USA	CA	91106-4005
4	2010 Wentworth Dr Billings MT 59105-3535 USA	MT	59105-3535
5	3372 S 7780 W Magna UT 84044-2041 USA	UT	84044-2041
State and Zip

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),400),100))
C2:C5	C2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),200),100))

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

If the double space after the state is the only double space in the text, then you could also use this for state

=MID(A2,FIND("  ",A2)+2,10)

.. and if all the Zip extractions are 10 characters long (as well as the only double space mentioned above) ..

=MID(A2,FIND("  ",A2)+2,10)

 

[drockdamian]

You're welcome. Thanks for the follow-up.

If the double space after the state is the only double space in the text, then you could also use this for state

=MID(A2,FIND("  ",A2)+2,10)

.. and if all the Zip extractions are 10 characters long (as well as the only double space mentioned above) ..

=MID(A2,FIND("  ",A2)+2,10)

Thanks Peter, I really appreciate it. How would I go about learning the first method?

 

[Peter_SSs]

How would I go about learning the first method?

What do you mean?

 

[drockdamian]

What do you mean?

I guess what I am asking is what does each part of the formula mean in order to extra that specific data from the string of information. Apologies for newbie question.

 

[Peter_SSs]

what does each part of the formula mean

SUBSTITUTE(A2," ",REPT(" ",100))
This puts 100 spaces everywhere there is currently a space character.
At the right side of the original string that will put 100 spaces between Zip & Country, 200 spaces between State and Zip, 100 spaces between City and State.

If you now take the RIGHT hand 400 characters of that you will get, working from the RIGHT, the Country, the 100 spaces between Zip & Country, the Zip, the 200 spaces between State & Zip, the State and some only of the 100 spaces between City and State.

If you now take the LEFT hand 100 characters of that you will get, working from the LEFT, the some only of the 100 spaces between City & State mentioned above, the State and some only of the 200 spaces between Sate & Zip.
TRIM then removes all the surrounding spaces (blue and red mentioned above) leaving just the State.

The other formula works in a similar manner.

 

[drockdamian]

Amazing...thank you so much for your time and effort on this topic. It is truly appreciated. I can't thank you enough. I hope you have a great holiday season!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
.. and your kind words. All the best to you too.