Quick Help with Excel

[scott4lud]

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


Worchester, MA 01602

 

[T. Valko]

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]

T. Valko,

Can you explain how your formula works?

 

[T. Valko]

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]

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

Yeah, it figures!

 

[T. Valko]

=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]

T. Valko,

Thank you soooooooooo much.

This goes into my archives.

This has a lot of potential.

 

[T. Valko]

T. Valko,

Thank you soooooooooo much.

This goes into my archives.

This has a lot of potential.

You're welcome!

 

[T. Valko]

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.