right, mid, left functions??

[syl_usmc]

I have a column with STATE & CITY information i.e. A1= VIRGINIA BEACH VA,
A2=YAKIMA WA, A3=RENO NV, etc. I'm able to extract the STATE digits using the
'right' function; however, I can't do the same for the CITY information due
to the different lenghts. Is there a formula to do this?
Please help.

 

[Mike H]

Hi,

Try this

=TRIM(LEFT(A1,LEN(A1)-2))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[David Biddulph]

If you want to remove the last 3 characters (space & state), then try
=LEFT(A1,LEN(A1)-3)

 

[T. Valko]

Try this...

A1 = VIRGINIA BEACH VA

B1 = extracted state:

=RIGHT(A1,2)

To extract the city:

=SUBSTITUTE(A1," "&B1,"")

 

[syl_usmc]

Mike,
Your're awsome! Thx so much

Syl

Hi,

Try this

=TRIM(LEFT(A1,LEN(A1)-2))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.