Parsing City State and Zip Code

**sandy1977** · 08-26-2014, 07:14 PM

I'm trying to split a column with cells containing City, state and zip code in them. I would like to have a column for City, State and Zip Code respectively. The attached file is just a sample, but I would be using cities from all over the US.

Thanks

**mehmetcik** · 08-26-2014, 07:27 PM

Try this it worked for me

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

**Richard Buttrey** · 08-26-2014, 07:27 PM

Hi,

State:

Formula:

=MID(E2,FIND(",",E2)+2,2)

Zip:

Formula:

(LEN(E2)-1=FIND(F2,E2),"",MID(E2,FIND(F2,E2)+3,5))

**sandy1977** · 08-26-2014, 07:35 PM

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

Thanks

**coolblue** · 08-27-2014, 03:47 AM

Originally Posted by sandy1977

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

Thanks

How are you trying to do this?
Post what you have written so far. The code in the attached sheet is original, how have you tried to modify it?

Meanwhile, here are some links that explain regular expressions...

http://strugglingtoexcel.wordpress.c...ssions-in-vba/
http://www2.hawaii.edu/~chenx/readin...vb_regexp.html
http://msdn.microsoft.com/en-us/libr...1x(VS.85).aspx

and this...
http://regex101.com/

**Richard Buttrey** · 08-27-2014, 04:31 AM

Originally Posted by sandy1977

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

Thanks

Hi,

Please note that you should explain all your requirements in the text of your post. There was no mention of VBA in your original.
In addition, please note the forum rules about starting new threads and linking to existing threads that deal with the same problem. As a minimum you should have posted a link to the other.

Finally if you do need VBA one way would be to use the method I suggested in post #3 an have VBA create and then copy the formulae for you. Then copy the formulae and paste them back as values with the macro.

**coolblue** · 08-26-2014, 07:48 PM

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

Sent from my iPad using Tapatalk

**sandy1977** · 08-26-2014, 10:03 PM

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

**watersev** · 08-27-2014, 05:49 AM

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

**coolblue** · 08-27-2014, 11:58 AM

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

RegExp.Pattern = Chr(160): Cell.Value2 = RegExp.Replace(Cell.Text, " ")

**sandy1977** · 08-27-2014, 08:53 AM

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.

Parsing City State and Zip Code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Re: Parsing City State and Zip Code

Thread Information

Users Browsing this Thread

Similar Threads

autofill city and state when typing in zip code

[SOLVED] Parsing Out City and State Formula

zip code to city, state function

Web lookup query (have: city & state | want: zip code)

zip code to city, state function

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions