Address Standardization Question

[help_stuck]

I'm trying to take a list of addresses and standardize them using a macro. Basically it's a bunch of replace commands at this point, but I'm having trouble with street names that contain the words I'm trying to abbreviate.

For example, I'm abbreviating East as E, but there are streets named Eastwood Lane, etc. The replace is catching that and changing it to Ewood. For words that finish a street name, like the road in Railroad, I've fixed it by putting a space before Road in the replace command, but I can't figure out what to do to catch the words at the beginning of street names. I could use "East ", but the problem there is that a lot of street names are formatted as "123 Main St East", with no space after East.

Is there a command that can direct the replace to only replace the word if it is not touching any other letters?

Here are a couple sample addresses:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD]100 COUCH AVENUE EAST[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD]100 EAST PROMENADE ST[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD]100 EASTLAND DR[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

Let Google Maps fix the addressing. This returns the address as Google Maps has it formatted.
Google does limit the number of calls to its API without a license, so if you have a lot to check, you have to do some (20-30) and then copy to values.

'Geocoded Address
Public Function GetGeoCodeAddress(Address As String) As String ', Longitude As Double, Latitude As Double, Status As String) As Boolean

' Declare variables and set return value to false by default
GetGeoCodeAddress = False
Dim response As DOMDocument60
Dim http As XMLHTTP60
Dim node As IXMLDOMNode
Dim nodes As IXMLDOMNodeList
Set http = New XMLHTTP60

' Read the data from the website
On Error Resume Next
' Open an XML request from Google using their GeoCode API
http.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Replace(Address, " ", "%20"), False
http.send
Set response = http.responseXML

' get the status node. This node tells you whether your search succeeded - OK means success. Any other status means some kind of error or address not found.
Set node = response.SelectSingleNode("/GeocodeResponse/status")
If node.nodeTypedValue <> "OK" Then
Status = node.nodeTypeString
Else
Set nodes = response.SelectNodes("/GeocodeResponse/result")
' check for multiple addresses if we found more than 1 result then error out.
If nodes.Length > 1 Then
MsgBox ("Found Multiple Matches for Address: " & Address)
Else
' grab the latitude and longitude from the XML response
Set node = response.SelectSingleNode("/GeocodeResponse/result/formatted_address")
GetGeoCodeAddress = node.nodeTypedValue

End If

End If

 Set http = Nothing
 Set response = Nothing

 End Function

 

[help_stuck]

Thanks for the suggestion, I'll give it a shot. Do I use this code as a macro? I've never interacted with an API before.

I've got like 30+ spreadsheets with 500-3000 addresses each, but I might be able to filter and run it on the addresses that need to be checked.

 

[SpillerBD]

Its a UDF - User Defined Formula
You can paste the code into a Module in your Personal(Macro) Workbook.

 

[tonyyy]

help_stuck,

Welcome to the Board.

You might also consider the following...

Sub Abbreviate_1020303()
Dim r As Range
Dim tmp As Variant
Dim i As Long

For Each r In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) ''''Change column letter to match your address column
    tmp = Split(r.Value, " ")
    For i = LBound(tmp) To UBound(tmp)
        If tmp(i) = "EAST" Then r.Value = Replace(r.Value, "EAST", "E")
        If tmp(i) = "WEST" Then r.Value = Replace(r.Value, "WEST", "W")
        ''''Add more "If tmp(i)..." statements as needed
    Next i
Next r
End Sub

If you're new to macros, please see the Contextures tutorial on adding code to a regular module.

The code uses the Split function to separate each address into individual words/numbers, using the space character as the delimiter. It then uses the Replace function similar to Excel's Find And Replace command.

Cheers,

tonyyy

 

[jeffcoleky]

Its a UDF - User Defined Formula
You can paste the code into a Module in your Personal(Macro) Workbook.

Can you elaborate on how to execute the function once it's in a module please? I found this while searching and it would help me out as well. Thanks.