Separating address

[Karthikeyan Devan]

Hi All,

Everyday I would be receiving a huge file which consist of address in Column A. It appears like below: I have provided three different examples:

2100 Jamieson Street Alexandria VA 22314

2800 Cottage Way, Sacramento, CA 95825

501 "I" Street - New FOB Sacramento CA 95814

Now, the problem is that, I want to separate the address such as, street i need in column B, city i need in Column C, State i need in column D and Post code in column E.

We can do this from text to columns using space option but the street would be divided into three since there are 3 spaces between door # and street.

Can we do this using VBA. Please aadvice...

 

[Karthikeyan Devan]

Hi All,

Any help on this would be greatly appreciated.

 

[justme1122]

Do can do this using the macro recorder:

I Got this:

Sub Macro1()
'
' Macro1 Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;c:\d......
        .Name = "07a"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[justme1122]

So, I should have said the above is opening a dif. workbook and importing the data from the other.

 

[Karthikeyan Devan]

Hi,

I think the macro recorder can only work when the Address are in same format. But the address are differing as i shown below:

2100 Jamieson Street Alexandria VA 22314

2800 Cottage Way, Sacramento, CA 95825

501 "I" Street - New FOB Sacramento CA 95814

Please assist...

 

[Ruddles]

A macro is merely a set of instructions which tells a computer what the user wishes to achieve. In order to write the macro, the user must work out what he wants it to do and how he wants it to do it.

In the case of your data, for example, you might be able to guarantee that the last "word" will always be a five-digit zipcode and the penultimate "word" will always be a two-character state abbreviation. If you can guarantee that, you can write VBA to extract those two elements.

However before you can divide the street name from the city name, you need to be able to express a rule for doing that. If there's a comma, you can use that. But what if there isn't a comma?

It might be "the street address is always exactly three words and the rest is the city name" or it might be "the city name is always a single word and everything before that is the street address (although I'm aware neither of those is true) or it might be "the city name will always be found in a lookup table which I will provide and everything else is the street address" or it might be "the street address will always end in St, Rd, Ave, etc".

So, you tell us: what's the rule for separating the street address and the city name if there's no comma separating them?

 

[Karthikeyan Devan]

Thanks for getting back on this. I hope you are getting what i need. I am happy now!

Let me tell you the rule.

Postal code will always be 5 digit and that needs to fit in column E.

State Abb would always be of two letters which should fit in column D.

As far as street address and city is concern, let me tell you few words by which the street name ends. Below are the example:

Street, st, Road, Rd, Way, Park, Avenue, Ave, Drive, HWY, NW, SW, Braodway, Blvd, building.

Please leave that particular address blank if any street address is not matching the above criteria. I hope all should fit in this criteria. If it is not matching i think hardly there would be two to three which we can manually edit. Please make that street address should fit in column B and City name in Column C.

Please let me know if you require any details.

 

[Ruddles]

Yes, if you could give me a couple of dozen lines of sample data which shows as many different formats as possible, that will be useful.

 

[Haseeb Avarakkan]

Hello,

Something like below..?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Data</td><td style="font-weight: bold;text-align: center;;">Street</td><td style="font-weight: bold;text-align: center;;">City</td><td style="font-weight: bold;text-align: center;;">State</td><td style="font-weight: bold;text-align: center;;">Post Code</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">2100 Jamieson Street Alexandria VA 22314</td><td style=";">2100 Jamieson Street</td><td style=";">Alexandria</td><td style=";">VA</td><td style="text-align: right;;">22314</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">2800 Cottage Way, Sacramento, CA 95825</td><td style=";">2800 Cottage Way,</td><td style=";">Sacramento,</td><td style=";">CA</td><td style="text-align: right;;">95825</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">501 "I" Street - New FOB Sacramento CA 95814</td><td style=";">501 "I" Street - New FOB</td><td style=";">Sacramento</td><td style=";">CA</td><td style="text-align: right;;">95814</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=TRIM(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">A2,C2,""</font>),D2,""</font>),E2,""</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">SUBSTITUTE(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">A2,D2,""</font>),E2,""</font>)</font>)," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">A2</font>)</font>)</font>),LEN(<font color="Green">A2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">SUBSTITUTE(<font color="Teal">A2,E2,""</font>)</font>)," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">A2</font>)</font>)</font>),LEN(<font color="Green">A2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">A2," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">A2</font>)</font>)</font>),LEN(<font color="Green">A2</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...

 

[Karthikeyan Devan]

Thanks Haseeb.. That's fine

However, I would like to automate it. Let me give few more examples to Ruddles and let me see if i can get VB coding to automate it.

Hey Ruddles,

Here are few examples:

<TABLE style="WIDTH: 303pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=404 border=0><COLGROUP><COL style="WIDTH: 303pt; mso-width-source: userset; mso-width-alt: 14774" width=404><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 303pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=404 height=20>1849 C St NW Washington DC 20240
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1335 East West Hwy Silver Spring MD 20910</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>601 4th St NW Washington DC 20001</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>19901 Germantown Road Germantown MD 20874</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3801 Nebraska Ave NW Washington DC 20393</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1000 Independence Ave SW Washington DC 20003</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4231 Suitland Rd Suitland MD 20746</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>600 Dulany St Alexandria VA 22203</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2430 E Street Complex NW Washington DC 20037</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>200 S Washington St Alexandria VA 22314</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2100 Jamieson Street Alexandria VA 22314</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>600 17th Street Washington DC 20500</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4303 Suitland Rd Suitland MD 20746</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4105 Suitland Rd Suitland MD 20746</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3100 Weslayin St, Houston TX 77027</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>125 Central Park, Los Alamos, TX </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>315 Broadway, Boulder CO 80305</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2800 Cottage Way, Sacramento, CA 95825</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>650 Cap-Mall, Sacramento, CA 95814</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>501 "I" Street - New FOB Sacramento CA 95814</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>280 S-1st St San Jose CA 95113</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>630 Sansome San Francisco CA 94111</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>345 Middlefield Rd Menlo CA 94025</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>312 N Spring St Los Angeles CA 90802</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11000 Wilshire Blvd Los Angeles CA 90025</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>411 W 4th St Santa Ana CA 92701</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>600 South Las Vegas Blvd Las Vegas NV 89101</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>501 West Ocean Blvd Long Beach CA 90802</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>230 North 1st Avenue Phoenix AZ 85025</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1137 N H St Lompoc CA 93436</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>300 7th St, SW Washington DC 20407</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1301 Pennsylvania Ave NW Washington DC 20004</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1700 Pennsylvania Ave NW Washington DC 20006</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3103 Pennsy Drive Landover MD 20785</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4600 Silver Hill Rd Suitland MD 20746</TD></TR></TBODY></TABLE>