Parsing City State and Zip Code

Try this it worked for me

http://www.dummies.com/how-to/conten...n-excel-2.html

Hi guys, I'm trying to write VBA code for it, please help.

Thanks

What specific problem do you have with your code? Can you post what you have tried so far?


Sent from my iPad using Tapatalk

coolblue, here is what I have, thanks to another thread where Leith Ross wrote some VBA code to separate an address string into separate parts using vba. I went ahead and tried to use Leith's code but the problem is that in some of the cells the ZIP CODE or CITY are missed and not copied to the corresponding column. Please see attached spreadsheet and run the Sub TestParseCityStateZip() to see what I'm talking about.

Any help is appreciated.


Thanks

hi sandy1977, VB option, please check attachment, press Run button or run code "test" (ALT+F8, select "test", Run)

coolblue and Richard, I apologize, I´m a newbie to this forum and to VBA as well. Certainly I appreciate the links and help given, I will definitely learn from this mistake(s). Richard I thought I was posting my thread under the VBA section of the forum but I will be more specific next time I post, I understand where you are coming from, it´s just easier for everyone reading my post.

And last but not least, WATERSEV, that works perfectly, just what I needed, thank you so much for your help and everyone that posted to this thread.

For anyone interested in root cause, the problem with the file is that there are non-breaking space characters (UNICODE 160) instead of normal (ASCII 32) space characters between the comma and the state code. This was causing the regular expression to fail and that's why the state codes were not printing. The original code from @leithross can be fixed in two ways...

1. Use \b instead of \s in the regular expressions to detect the word boundaries (as in the solution from @waterserv)
2. Use a regular expression to replace the non-breaking spaces with regular ones

Please Login or Register  to view this content.