Extract Numbers in a certain string

[Stefan_Sand]

Hello,

i have a huge Excel XML list with some numbers in it. I want to extract some (not all) of the numbers to columns B:F.
Is there an easy way to extract the numbers for BEZNR, ZBEZNR, ZGEBNR, ZBEZ, ZGEB?
Thanks for some help,
Stefan

pleas see the example string below and the attached file with some data

<li><strong><span class="atr-name">BEZNR</span>:</strong> <span class="atr-value">19</span></li>
<li><strong><span class="atr-name">ZBEZNR</span>:</strong> <span class="atr-value">1</span></li>
<li><strong><span class="atr-name">ZGEBNR</span>:</strong> <span class="atr-value">9</span></li>
<li><strong><span class="atr-name">BEZ</span>:</strong> <span class="atr-value">19</span></li>
<li><strong><span class="atr-name">ZBEZ</span>:</strong> <span class="atr-value">1901</span></li>
<li><strong><span class="atr-name">ZGEB</span>:</strong> <span class="atr-value">19019</span></li>
<li><strong><span class="atr-name">FLAECHE</span>:</strong> <span class="atr-value">887629.5989</span></li>
<li><strong><span class="atr-name">UMFANG</span>:</strong> <span class="atr-value">7788.55</span></li>
<li><strong><span class="atr-name">AKT_TIMESTAMP</span>:</strong> <span class="atr-value">18.04.2013 08:23:11</span></li>
<li><strong><span class="atr-name">SE_SDO_ROWID</span>:</strong> <span class="atr-value">371287</span></li>

[HansV]

That looks like HTML to me instead of XML.
Will all cells contain all the elements BEZNR etc. in the same order, or could some elements be missing, or could the order of the elements be different in some cells?

[Stefan_Sand]

The structure is always the same. The masterfile is a kml google file.
Best regards,
Stefan

[HansV]

Try this macro:

Code: Select all

Sub ExtractData()
    Dim r As Long
    Dim m As Long
    Dim strVal As String
    Dim p1 As Long
    Dim p2 As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 2 To m
        strVal = Cells(r, 1).Value
        p1 = 0
        For c = 2 To 6
            p1 = InStr(p1 + 1, strVal, "atr-value")
            If c = 5 Then
                ' Skip BEZ
                p1 = InStr(p1 + 11, strVal, "atr-value")
            End If
            p2 = InStr(p1 + 11, strVal, "<")
            Cells(r, c).Value = Mid(strVal, p1 + 11, p2 - p1 - 11)
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

[Stefan_Sand]

cool, many thanks!
Stefan