Try this...What formula would i use to just show the "MA" if below is in cell A1?
Worchester, MA 01602
=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))T. Valko,
Can you explain how your formula works?
ADVERTISEMENT
Yeah, it figures!Hopefully when I hit the Submit Reply button the forum software doesn't strip out all those space characters!
OK, let's start over!=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!
You're welcome!T. Valko,
Thank you soooooooooo much.
This goes into my archives.
This has a lot of potential.
Sometimes we can't see the forest because of the trees!What formula would i use to just show the "MA" if below is in cell A1?
Worchester, MA 01602