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
Super-AntiSpyware - where do I go to get it?
Super-antispyware - that IS the GOOD one, isn't it?...
Janey - formerly jane2
XP backup reinstall
Back again, considering doing a XP backup (what's best...
Can't figure out why my Firefox Colorful Tabs don't display
My colorful tabs stopped working and I can't figure...
MacBook--Image Frozen on Screen
Anyone know what this means? (or how to rotate the...
I-Pad Backup Help or Need?
I need advise on or need for backing up my wife's I-pad. Wife...
Sponsored Products
Nikko Brushed Nickel Three-Light 18-Inch Pendant with Square Shade
$246.00 | Bellacor
Jonathan Adler Parker Nickel Plug-In Swing Arm Wall Lamp
Lamps Plus
Parson Chandelier
Click Clack Checkered Sofa Bed
Tech Lighting | 12V 150W Single-Feed Surface Transformer Wall MonoRail
$272.00 | YLighting
RGB 5050 Pixel Control LED Super Flat Rope, 56/m, White Finish, Meter
People viewed this after searching for:
© 2015 Houzz Inc. Houzz® The new way to design your home™