Text to column using multiple spaces?? CITY STATE ZIP

  • Thread starter Thread starter PAkerly
  • Start date Start date
P

PAkerly

Hello I currently have a column that is CITY STATE ZIP

I would like to make this 3 seperate columns CITY, STATE, ZIP

How can I do this? I cant use space because sometimes the city has 2
parts:

BUFFALO NY 11222

or sometimes it is:

BUFFALO CITY NY 11223

any ideas? thanks.
 
Hello I currently have a column that is CITY STATE ZIP

I would like to make this 3 seperate columns CITY, STATE, ZIP

How can I do this?  I cant use space because sometimes the city has 2
parts:

BUFFALO NY 11222

or sometimes it is:

BUFFALO CITY NY 11223

any ideas?  thanks.
Click to expand...

I can't solve your problem just off. But here are some things
I'd be thinking about if it were my problem.

Are the fields fixed length? State and zip-code might be. Maybe
the city is padded with blanks?

Can you use something like the RIGHT function? Maybe peel off
the last 5 chars for the zip, then any white space, then two for the
state, then any more white space, then everything else is the city.

Do you have to do it using worksheet features? Or can you
write some VB to do it?

Can you be sure that your data always has the zip code in 5 digit
format?
There are some zips that are more complicated than that, right?
Something about sub-zips or something. I'm from Canada and
don't use zip codes very often.

Can it ever be an address outside the USA?
Socks
 
hi,

you can use a custum function (VBA)

=ZipCode(A1)

Function ZipCode(rng As Range)
x = Split(rng, " ")
ZipCode = x(UBound(x))
End Function
 
Back
Top