Free Macro to convert a column with City State Zip to 3 columns

Hi all. I've been a lurker for 2023 days according to the banner. I've come here a lot looking for help on formulas so it is time to give back.

I run a print shop and I get mailing lists all the time. For some reason beyond my comprehension a lot come with "city state zip" all in one column. Yeah, if you're lucky, you can uses text to columns and break it on the comma first to get "City" and "State Zip" and then by space to break "State Zip" into 2 columns. Then go back and do a find and replace on the state column to get rid of the space at the start.

But I find that on long lists I have a problem with manual entries. What if there is no comma? What if there are extra spaces? It's a mixed bag. And you can't just 'text to columns' on Space, because what if the city name has two words? Like "New York" or "Las Vegas" ? That throws it off too.

So after years of dealing with this by hand, I figured it was time to put together a macro (and a function which could have been part of the macro) that solves 'most' of it. I've added a lot of comments to try and explain what is going on in the macro. But basically, it strips out extra spaces and puts everything into an array based on space as a delimiter. Then works from the end of the array to find the zip, the state, and then adds the rest together to get the city. It preserves leading zeros and Zip+4 zip codes by using a '.

Known Failures:
1. This is for US addressing.
2. If your state is two words instead of a 2 letter abbreviation, it will produce the wrong results, but not tell you that.

I hope you find this useful, if you have any corrections/improvements, I'd love to hear them.

after installing it the basic usage is as follows:

1. make sure you have 3 empty columns to the right of the range that you want to convert.
2. Run the macro.
3. It will give a warning about saving work and such. Click OK
4. Select the range that you want to convert and Click OK.
5. That's it. Check the work.

Please Login or Register  to view this content.

Obviously, this macro is free for personal use, or use at/in your business, but don't sell it or incorporate it into a paid product. Cool? Thanks.