Google Distance Matrix API - Distance and Time

Hi All,

I have found a excel function on the net the works quite well for what I need. Only problem is I cant seem to split the 'Time' and 'Distance' into 2 separate cells. It returns the distance+time in the same cell.

I have got it to only return the distance or time also but still cannot split the two values into separate cells.

Any assistance would be appreciated.

Download Working File: Download Working File https://www.box.com/s/3ai8xgasra8kpsgt40lq

Public Function get_dis_and_time _
( _
origin_street As String, origin_city As String, _
origin_state As String, origin_country As String, _
destination_street As String, destination_city As String, _
destination_state As String, destination_country As String _
)

' Read more about Google API's here
'https://developers.google.com/maps/documentation/distancematrix/


Dim surl                As String
Dim oXH                 As Object
Dim bodytxt             As String
Dim tim_e               As String
Dim distanc_e           As String


surl = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & _
Replace(origin_street, " ", "+") & "+" & Replace(origin_city, " ", "+") & "+" & Replace(origin_state, " ", "+") & "+" & Replace(origin_country, " ", "+") & _
"&destinations=" & _
Replace(destination_street, " ", "+") & "+" & Replace(destination_city, " ", "+") & "+" & Replace(destination_state, " ", "+") & "+" & Replace(destination_country, " ", "+") & _
"&mode=driving&sensor=false&units=imperial"

' units=imperial
'if u want to show distance in kms change unit to metric
'https://developers.google.com/maps/documentation/distancematrix/#unit_systems
'units=metric (default) returns distances in kilometers and meters.
'units=imperial returns distances in miles and feet.

Set oXH = CreateObject("msxml2.xmlhttp")
    
    With oXH
        .Open "get", surl, False
        .send
        bodytxt = .responseText
    End With

bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, "<text>") - 5)
tim_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)


bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, "<text>") - 5)
distanc_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)

get_dis_and_time = tim_e & " | " & distanc_e  'this works but returns values in single cell (=get_dis_and_time(A3,B3,C3,D3,E3,F3,G3,H3)

'get_dis_and_time = tim_e   'this works also - returns time only (=get_dis_and_time(A3,B3,C3,D3,E3,F3,G3,H3)

'get_time = tim_e    'this does not work (=get_time(A3,B3,C3,D3,E3,F3,G3,H3)


Set oXH = Nothing

End Function