Parse address from one cell into many

[austenr]

Here is what I need to do:
In columns C,D,E or F are parts of addresses.

Starting in column C, I need a way to split the address out into main address, city, state, zip.
I also know that to accomplish this you need to read
backwards in the cell because if you find the zip code, it will always be
the last field. If the cell being searched contains the zip code, there
is no need to read the other columns because you are done.
I have searched the internet and the forums and cannot find an example of
how to do this. Attached is a small file that illustrates this problem. Thanks

[Erdin? E. Ka]

Hi,

For split digits use this function:

Function Split_Digits(CeL)
Dim i As Integer
For i = 1 To Len(CeL)
DiGiT = Mid(CeL, i, 1)
If IsNumeric(DiGiT) = True Then
Split_Digits = Split_Digits & DiGiT
Split_Digits = CStr(Split_Digits)
End If
Next i
End Function



For split texts use this function:

Function Split_Texts(CeL)
Dim i As Integer
For i = 1 To Len(CeL)
DiGiT = Mid(CeL, i, 1)
If Not IsNumeric(DiGiT) = True Then
Split_Texts = Split_Texts & DiGiT
Split_Texts = CStr(Split_Texts)
End If
Next i
End Function

[mvidas]

Austen,

I made something similar to this a year and a half ago for a big project some of my coworkers were doing. Based on your example I modified it to find your addresses, unzip it to your AddIns folder (C:\Documents and Settings\austenr\Application Data\Microsoft\AddIns\) then go to Tools / Addins from excel, click browse, and select AustenAddr, and click ok.

A new menu should be added, run it when your sample sheet (or similar real sheet) is the activesheet. The first time you run it, it WILL take a few minutes since it has to retrieve zip code data from the internet. But each subsequent run will be faster, I just removed it from the addin to save on filesize (~3.5mb with the zip code data, 60k without)

[austenr]

Thanks buddy you dont know how much time that saved me!!!

[austenr]

Matt,

Having trouble running this addin. It alomst immediatly goes to "Not Responding". Any advice?

[mvidas]

Odd, I don't know why it would say that. As I said it will take about 5 minutes to run and retrieve the data from the internet, can you press control-break to stop the running of it when it is not-responding?
If need be I can create the list and include it with the addin, ill start it now and post it in a couple minutes

[mvidas]

Too big to attach here (632kb), uploaded to http://www.mvidas.com/AustenAddr.zip

[austenr]

Thanks buddy

[austenr]

Hi Matt,

It works great at home on my broadband connection. That is probably the issue at work. Anyway I will try it again tomorrow. This is saving me a ton of work!! Can't thank you enough. This one is definatly solved!! :friends:

[austenr]

here it is.

[mvidas]

Interesting... did you happen to run it 6 times??
I have it so it adds those 4 columns when run.. if you only ran it once it must have somehow repeated this block 6 times Set ADDR = Range("IV1").End(xlToLeft).Offset(0, 1).EntireColumn
ADDR.Cells(1) = "Address"
Set CITY = Range("IV1").End(xlToLeft).Offset(0, 1).EntireColumn
CITY.Cells(1) = "City"
Set ST = Range("IV1").End(xlToLeft).Offset(0, 1).EntireColumn
ST.Cells(1) = "State"
Set ZIP = Range("IV1").End(xlToLeft).Offset(0, 1).EntireColumn
ZIP.Cells(1).Value = "zip code"Also, if you won't be having column titles in there, change the 2 to a 1 in this line: For r = 2 To lr
Also, if you couldn't tell I moved your post :)

[austenr]

Hey thats fine. Now it hangs up on me again. i think i will start with a fresh spreadsheet and see what happens. So you got it to run normally on your machine i suppose.

[mvidas]

Yeah.. first time :dunno
I think your computer there is haunted by that vb project your former coworker left you

[austenr]

yea. it is in fact Halloween month. We live near Salem MA and my wife has to fight the tourists this time of month to find parking at work, although if you do any research on the subject, the people were not really witches, they went mad from eating molded bread and everyone thought they were witches. :pumpkin:

[mvidas]

interesting little fact I had never heard before :) good thing im allergic to most molds!

[austenr]

:)