Return to the Computer Help Forum | Post a Follow-Up

 o
converting address in Excel?

Posted by caliloo (My Page) on
Tue, Sep 25, 12 at 17:48

I have an excel spread sheet that some prior brain trust stored the address in a single cell -

345 Oak St Anytown OH 99999

I havent looked at all the entries, but most look like there is no punctuation between the street, city, state, zip. Can I convert the space delimited address field into individual street, city, state, zip cells in a new workbook?

I am running office 2010

Thanks
Alexa


Follow-Up Postings:

 o
RE: converting address in Excel?

I don't have "the" answer but...

I suspect you'd have to use some sort of filter, or "regular expressions"?

You'd probably want to start at the right and grab the zip code. Maybe if they're all 5 digit zips you could use something like: =RIGHT(RowCol,5) ????

If the states are all entered as two digit state codes they would probably be just as easy. Perhaps you'd "find" the next to the last "space" and grab the next two characters? Sorry, I'm not an Excel expert, or even proficient.

The difficult part would be separating the street address from the city since the city could multiple words. Maybe you could use a table of valid cities and do lookups somehow? It would of course be much easier if commas had been used. :)

Even then you'd probably have to make assumptions that would work in the majority of cases, but not all, then go though them manually, one by one, correcting them as you go. Doable, unless you have tens of thousands.

There's maybe a ready-made solution on the web, a macro perhaps? I'm sure others have had to deal with the same problem. Maybe check an Excel forum?


 o
RE: converting address in Excel?

Hi caliloo,

See if anything here helps you.

Joe

Here is a link that might be useful: Split-address-field-into-separate-columns


 o
RE: converting address in Excel?

I have Word 2010 and had a similar problem. It was a border line that I could not remove. I found the instructions by Googling. I opened a new document, went to file, options, proofing, auto correct options, "auto format as you type" and removed a check mark in the right box. The article suggesting removing all of the check marks in that area. I did and have not had any more formatting problems.


 o Post a Follow-Up

Please Note: Only registered members are able to post messages to this forum.

    If you are a member, please log in.

    If you aren't yet a member, join now!


Return to the Computer Help Forum

Information about Posting

  • You must be logged in to post a message. Once you are logged in, a posting window will appear at the bottom of the messages. If you are not a member, please register for an account.
  • Please review our Rules of Play before posting.
  • Posting is a two-step process. Once you have composed your message, you will be taken to the preview page. You will then have a chance to review your post, make changes and upload photos.
  • After posting your message, you may need to refresh the forum page in order to see it.
  • Before posting copyrighted material, please read about Copyright and Fair Use.
  • We have a strict no-advertising policy!
  • If you would like to practice posting or uploading photos, please visit our Test forum.
  • If you need assistance, please Contact Us and we will be happy to help.


Learn more about in-text links on this page here