Help on Extract portion of string in the right way

[gonen]

Hi

I have a very long string like this:


<?xml version="1.0" encoding="UTF-8"?>
<DirectionsResponse>
<status>OK</status>
<route>
<summary>?A16?‏</summary>
<leg>
<step>
<travel_mode>DRIVING</travel_mode>
<start_location>
<lat>48.8583500</lat>
<lng>2.3532800</lng>
</start_location>
<end_location>
<lat>48.8598600</lat>
<lng>2.3543300</lng>
</end_location>
<polyline>
<points>useiH_sjMcCuAgBgAaAs@</points>
</polyline>
<duration>
<value>27</value>
<text>1 דקה</text>
</duration>
<distance>
<value>184</value>
<text>0.2 ק&quot;מ</text>
</distance>
</step>
<step>
<travel_mode>DRIVING</travel_mode>
<start_location>
<lat>48.8598600</lat>
<lng>2.3543300</lng>
</start_location>
<end_location>
<lat>48.8603100</lat>
<lng>2.3530200</lng>
</end_location>
<polyline>
<points>c}eiHqyjMw@vCa@lB</points>
</polyline>
<duration>


I need to extract only the numbers which are wrapped with <lat>48.8598600</lat>

or

<lng>2.3530200</lng>

so that at the end of the process I will have a new string with pairs of numbers as:


48.8598600,2.3543300
48.8603100,2.3530200
.
.
.

I need advise on the most efficient method to do it

Thank you !!

[GTO]

I copied the string to a textfile; not sure if the characters will read correctly from your source, but here's a shot:

In a Standard Module:
Option Explicit
Sub Example()
Dim FSO As Object ' FileSystemObject
Dim fsoTStream As Object ' TextStream
Dim strTemp As String

'// psuedo source //
strTemp = ThisWorkbook.Path & "\Test2.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set fsoTStream = FSO.OpenTextFile(strTemp, 1, False, &HFFFFFFFE)
strTemp = fsoTStream.ReadAll
fsoTStream.Close

If RetVal(strTemp) Then
MsgBox strTemp, vbOKOnly Or vbInformation, vbNullString
End If
End Sub

Function RetVal(IOString As String) As Boolean
Dim REX As Object ' RegExp
Dim rexMC As Object ' MatchCollection
Dim rexM As Object ' Match
Dim strTemp As String

Set REX = CreateObject("VBScript.RegExp")

With REX
.Global = True
.IgnoreCase = True
.MultiLine = False
.Pattern = _
"(\<lat\>)(\[color=""[a-zA-Z]+""\])?([0-9\.]+)(\[\/color\])?" & _
"(\<\/lat\>)(\s*)(\<lng\>)(\[color=""[a-zA-Z]+""\])?([0-9\.]+)" & _
"(\[\/color\])?(\<\/lng\>)"

If .Test(IOString) Then
Set rexMC = .Execute(IOString)

For Each rexM In rexMC
strTemp = strTemp & rexM.SubMatches(2) & ", " & rexM.SubMatches(8) & vbCrLf
Next

IOString = Left$(strTemp, Len(strTemp) - 2)
RetVal = True
End If
End With
End Function
Hope that helps,

Mark

[gonen]

It does !!! I will try it

thanks !

[GTO]

:dunno I should have thought to include the textfile I tested against. Brain fallout is what I call it:doh:

By the way, I get:
48.8583500, 2.3532800
48.8598600, 2.3543300
48.8598600, 2.3543300
48.8603100, 2.3530200
for a return.