converting address in Excel?

calilooSeptember 25, 2012

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



Thank you for reporting this comment. Undo

Hi caliloo,

See if anything here helps you.


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

    Bookmark   September 26, 2012 at 9:42PM
Thank you for reporting this comment. Undo
Richard (chuggerguy)

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?

    Bookmark   September 26, 2012 at 9:42PM
Thank you for reporting this comment. Undo

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.

    Bookmark   September 26, 2012 at 11:22PM
Sign Up to comment
More Discussions
OK, turned my back, and the kids are playing with
backgrounds, and making the viewing easier on the eyes....
What's with this new Houzz thing?
Been away a couple days, and came back to this, what...
Google Stylish add-on help
I have Google Stylish add-on but don't know how to...
Lenovo's "malware" not just Lenovo's.
It's been found in a dozen or so apps, some pre-installed,...
Win 8 network settings
I need to export my network settings to a flash drive...
People viewed this after searching for:
© 2015 Houzz Inc. Houzz® The new way to design your home™