Quick Help with Excel

scott4lud

New Member
Joined
Jan 25, 2011
Messages
45
What formula would i use to just show the "MA" if below is in cell A1?


Worchester, MA 01602
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
T. Valko,

Can you explain how your formula works?
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))

A1 =
Worchester, MA 01602

Basically, we pad the spaces around "MA" then we use the RIGHT and LEFT functions to "narrow down" the string and finally we TRIM off the excess spaces that are left over.

For the purposes of this explanation I'll use smaller numbers to replace 100, 200 and 100. I'll use 10 and 20.

SUBSTITUTE(A1," ",REPT(" ",10)) =
"Worchester, MA 01602"

So we've padded a bunch of spaces around "MA".

RIGHT("Worchester, MA 01602",20) =
" MA 01602"

So we've "narrowed down" the string.

LEFT(" MA 01602",10) =
" MA "

So we've "narrowed down" the string even further.

TRIM(" MA ") =
MA

So we've used TRIM to remove all the excess spaces that were left.

Hopefully when I hit the Submit Reply button the forum software doesn't strip out all those space characters!
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Hopefully when I hit the Submit Reply button the forum software doesn't strip out all those space characters!
Yeah, it figures! :LOL:
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))

A1 =
Worchester, MA 01602

Basically, we pad the spaces around "MA" then we use the RIGHT and LEFT functions to "narrow down" the string and finally we TRIM off the excess spaces that are left over.

For the purposes of this explanation I'll use smaller numbers to replace 100, 200 and 100. I'll use 10 and 20.

SUBSTITUTE(A1," ",REPT(" ",10)) =
"Worchester, MA 01602"

So we've padded a bunch of spaces around "MA".

RIGHT("Worchester, MA 01602",20) =
" MA 01602"

So we've "narrowed down" the string.

LEFT(" MA 01602",10) =
" MA "

So we've "narrowed down" the string even further.

TRIM(" MA ") =
MA

So we've used TRIM to remove all the excess spaces that were left.

Hopefully when I hit the Submit Reply button the forum software doesn't strip out all those space characters!
OK, let's start over! :mad:

Imagine the underscores _ are spaces.

A1 =
Worchester,_MA_01602

Basically, we pad the spaces around "MA" then we use the RIGHT and LEFT functions to "narrow down" the string and finally we TRIM off the excess spaces that are left over.

For the purposes of this explanation I'll use smaller numbers to replace 100, 200 and 100. I'll use 10 and 20.

SUBSTITUTE(A1,"_",REPT("_",10)) =
Worchester,__________MA__________01602

So we've padded a bunch of spaces around "MA".

RIGHT("Worchester,__________MA__________01602",20) =
___MA__________01602

So we've "narrowed down" the string.

LEFT("___MA__________01602",10) =
___MA_____

So we've "narrowed down" the string even further.

TRIM("___MA_____") =
MA

So we've used TRIM to remove all the excess spaces that were left.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

T. Valko,

Thank you soooooooooo much.

This goes into my archives.

This has a lot of potential.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
What formula would i use to just show the "MA" if below is in cell A1?


Worchester, MA 01602
Sometimes we can't see the forest because of the trees!

If the cell entry is always in the same format:

[City][comma][single space][2 letter state abbreviation][single space][5 digit zip code]

Then:

=MID(A1,FIND(",",A1)+2,2)

The other formula I suggested is a generic formula that extracts the next to last word in a string which just happens to also work in this application.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top