Quick Help with Excel

scott4lud

New Member
What formula would i use to just show the "MA" if below is in cell A1?

Worchester, MA 01602

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
What formula would i use to just show the "MA" if below is in cell A1?

Worchester, MA 01602
Try this...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))

hiker95

Well-known Member
T. Valko,

Can you explain how your formula works?

T. Valko

Well-known Member
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

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

T. Valko

Well-known Member
=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!

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

T. Valko,

Thank you soooooooooo much.

This goes into my archives.

This has a lot of potential.

T. Valko

Well-known Member
T. Valko,

Thank you soooooooooo much.

This goes into my archives.

This has a lot of potential.
You're welcome!

T. Valko

Well-known Member
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.

Replies
13
Views
95
Replies
3
Views
37
Replies
5
Views
100
Replies
0
Views
27
Replies
1
Views
145