excelformula

=GetDistance(CONCATENATE(SanitizeGeo(I2), ", ", J2, " ", K2), CONCATENATE(SanitizeGeo(M2), ", ", N2, " ", O2))

excelschema

Provider	ServiceDesc	TripDate	TripTimeAMPM	TripPUCode	ClientFullName	TripNo	TripOriginPh	TripOriginStreet	TripOriginCity	TripOriginState	TripOriginZIP	TripDestStreet	TripDestCity	TripDestState	TripDestZIP	TripComments	ProvCode	Distance

geodistance.vba

'Calculate Google Maps distance between two addresses
'Based on the code found here http://analystcave.com/excel-calculate-distances-between-addresses/
Public Function GetDistanceMoCo(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    
    start = start + " near Montgomery County, MD"
    
    dest = dest + " near Montgomery County, MD"
    
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regEx = CreateObject("VBScript.RegExp"): regEx.Pattern = """value"".*?([0-9]+)": regEx.Global = False
    Set matches = regEx.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistanceMoCo = CDbl(tmpVal / 1609.34)
    
    Exit Function
ErrorHandl:
    GetDistanceMoCo = -1
End Function

'Calculate Google Maps distance between two addresses
'Based on the code found here http://analystcave.com/excel-calculate-distances-between-addresses/
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "&destinations="
    lastVal = "&mode=car&language=en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regEx = CreateObject("VBScript.RegExp"): regEx.Pattern = """value"".*?([0-9]+)": regEx.Global = False
    Set matches = regEx.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal / 1609.34)
    
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function

' Get Non-Alpha numeric stuff out
Public Function SanitizeGeo(location As String)
    Set re = CreateObject("VBScript.RegExp"): re.Pattern = "([a-zA-Z0-9 /]+).*"
    Set inputMatches = re.Execute(location)
    
    SanitizeGeo = inputMatches(0).SubMatches(0)
    
End Function