Separating an address string into separate parts using vba

Hello,

I have an address string that I need to split up into three cells using vba with the mid, right, and left functions. The problem that I'm having is that I can't seem to formulate the functions in which they can handle different forms of the address.

For example,

New York, NY 23658 should return New York in one cell, NY in the next, and 23658 in the next.

New York, NY should return New York in one cell, NY in the next, and nothing in the next because there isn't a zip code.

The code I have for the first example is:

Please Login or Register  to view this content.

This code works. I just need to amend it for the second example.

Thanks in advance,

Hello adhbrown,

You may be able to use this macro. It is part of a lager macro I wrote to handle mail addresses for both the US and Canada. It may require some tweaking to make it work with your code. The macro examines the cells in a column. If those cells contain an address line with the city, state, and zip (optional) then first cell will be the city name, the cell one column to the right will be the state, and the cell two cells to the right will be the Zip, if there is one.

Please Login or Register  to view this content.

Example: Using the Macro

Please Login or Register  to view this content.