Splitting City State Zip

Excel 2000:

I'm trying to split the city state zip field into individual fields. I was
able to get the zip and state by using the folllowing formulas:

State: =LEFT(RIGHT(M2,10),3)

Zip: =RIGHT(M2,5)

But, I can't seem to get the city. The problem being no comma betwee the
city and St and cities with two name:

Loris SC 29573
Myrtle Beach SC 29577
Little River SC 29588

Any help will be appreciated.


Pete Provencher

How about

=TRIM(LEFT(TRIM(A1),LEN(TRIM(A1))-9))

If your addresses are all well-formed (e.g., no extra spaces), that
reduces to

=LEFT(A1,LEN(A1)-9)


In article <OLn$XYVDFHA.1040@TK2MSFTNGP09.phx.gbl>,
"Pete Provencher" <pprovencher@sitetechsystems.com> wrote:

> Excel 2000:
>
> I'm trying to split the city state zip field into individual fields. I was
> able to get the zip and state by using the folllowing formulas:
>
> State: =LEFT(RIGHT(M2,10),3)
>
> Zip: =RIGHT(M2,5)
>
> But, I can't seem to get the city. The problem being no comma betwee the
> city and St and cities with two name:
>
> Loris SC 29573
> Myrtle Beach SC 29577
> Little River SC 29588
>
> Any help will be appreciated.
>
>
> Pete Provencher

That worked1 Thanks a lot.

Pete Provencher
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-17E049.13523907022005@msnews.microsoft.com...
> How about
>
> =TRIM(LEFT(TRIM(A1),LEN(TRIM(A1))-9))
>
> If your addresses are all well-formed (e.g., no extra spaces), that
> reduces to
>
> =LEFT(A1,LEN(A1)-9)
>
>
> In article <OLn$XYVDFHA.1040@TK2MSFTNGP09.phx.gbl>,
> "Pete Provencher" <pprovencher@sitetechsystems.com> wrote:
>
> > Excel 2000:
> >
> > I'm trying to split the city state zip field into individual fields. I
was
> > able to get the zip and state by using the folllowing formulas:
> >
> > State: =LEFT(RIGHT(M2,10),3)
> >
> > Zip: =RIGHT(M2,5)
> >
> > But, I can't seem to get the city. The problem being no comma betwee the
> > city and St and cities with two name:
> >
> > Loris SC 29573
> > Myrtle Beach SC 29577
> > Little River SC 29588
> >
> > Any help will be appreciated.
> >
> >
> > Pete Provencher