Separate an address string into parts using VBA

[adhbrown]

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:


Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 1) = Trim(Left(CityStateZip,Find(" ",CityStateZip) Len(CityStateZip) - 10))
Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 2) = Trim(Mid(CityStateZip, Len(CityStateZip) - 8, 4))
Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 3) = Trim(Right(CityStateZip, 5))

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

Thanks in advance,

 

[RoryA]

Cross-posted here.