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.

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.


"syl_usmc" wrote:

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.

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

"syl_usmc" wrote in message
...
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.

Try this...

A1 = VIRGINIA BEACH VA

B1 = extracted state:

=RIGHT(A1,2)

To extract the city:

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

--
Biff
Microsoft Excel MVP


"syl_usmc" wrote in message
...
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,
Your're awsome! Thx so much

Syl

"Mike H" wrote:

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.


"syl_usmc" wrote:

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.