=IMPORTXML(A2;"//div[@class='_b0ke8']/a/@href")

I'm trying to collect phone numbers via Excel (IMPORTXML) and XPath.

But it only collects one (first) option. +7771128919 what am I doing wrong?

<div class="17zgb6" data-divider="true" data-divider-shifted="true">
        <div class="before">
            <div class="1ifozou">
                <svg xmlns="; width="24" height="24" viewBox="0 9 24 24" fill="#0288ef">
                    <path d="M14 14.1 18.88 18.47 18.47 18.88 14 14.41 9.53 18.88 9.12 18.47 13.6 14 9.12 9.53 9.53 9.12 14 13.6 18.47 9.12 18.88 9.53z"></path>
                </svg>
            </div>
            <div class="49xkir">
                <div class="b0kes">
                    <a class="21cm9s8" href="tel:+7771128919" target="_blank">
                        <bdo dir="ltr">+7 701-211-89-19</bdo>
                    </a>
                </div>
                <div class="b0kes">
                    <a class="21cm9s8" href="tel:+77172485983" target="_blank">
                        <bdo dir="ltr">+7 (7172) 48-69-88</bdo>
                    </a>
                </div>
                <div class="b0kes">
                    <a class="21cm9s8" href="tel:+77022543764" target="_blank">
                        <bdo dir="ltr">+7 702-254-37-64</bdo>
                    </a>
                </div>
            </div>
        </div>
    </div>
</div>

//bdo[@dir='ltr']

I want to get all three numbers.

=IMPORTXML(A2;"//div[@class='_b0ke8']/a/@href")

I'm trying to collect phone numbers via Excel (IMPORTXML) and XPath.

But it only collects one (first) option. +7771128919 what am I doing wrong?

<div class="17zgb6" data-divider="true" data-divider-shifted="true">
        <div class="before">
            <div class="1ifozou">
                <svg xmlns="http://www.w3./2000/svg" width="24" height="24" viewBox="0 9 24 24" fill="#0288ef">
                    <path d="M14 14.1 18.88 18.47 18.47 18.88 14 14.41 9.53 18.88 9.12 18.47 13.6 14 9.12 9.53 9.53 9.12 14 13.6 18.47 9.12 18.88 9.53z"></path>
                </svg>
            </div>
            <div class="49xkir">
                <div class="b0kes">
                    <a class="21cm9s8" href="tel:+7771128919" target="_blank">
                        <bdo dir="ltr">+7 701-211-89-19</bdo>
                    </a>
                </div>
                <div class="b0kes">
                    <a class="21cm9s8" href="tel:+77172485983" target="_blank">
                        <bdo dir="ltr">+7 (7172) 48-69-88</bdo>
                    </a>
                </div>
                <div class="b0kes">
                    <a class="21cm9s8" href="tel:+77022543764" target="_blank">
                        <bdo dir="ltr">+7 702-254-37-64</bdo>
                    </a>
                </div>
            </div>
        </div>
    </div>
</div>

//bdo[@dir='ltr']

I want to get all three numbers.

• xpath

Share Improve this question Follow edited Feb 18 at 16:42 I.sh. 2,22122 gold badges1313 silver badges4444 bronze badges asked Feb 2 at 20:18 Villmark DeschanelVillmark Deschanel 1122 bronze badges 2

• isn't the class b0kes instead of _b0ke8? – Fravadona Commented Feb 2 at 22:29
• I think it is not possible. Either you use a Programming Language to loop through the iterations using RegExp(local copy), or web scrape the numbers(website). – nikitimi Commented Feb 3 at 9:53

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Excel will only give you one result. A possible approach could be something like this:

Create VBA code like this:

Function GetXMLValues(xmlUrl As String, xPath As String) As String
    Dim xml As Object
    Set xml = CreateObject("MSXML2.DOMDocument")
    xml.async = False
    xml.Load xmlUrl
    
    Dim nodeList As Object
    Set nodeList = xml.SelectNodes(xPath)
    
    Dim i As Integer
    Dim results As String
    results = ""
    
    For i = 0 To nodeList.Length - 1
        results = results & nodeList.Item(i).Text & vbNewLine
    Next i
    
    GetXMLValues = results
End Function

And use it in Excel like this:

=GetXMLValues(A2, "//div[@class='_b0ke8']/a/@href")

Your sample isn't well formed but since it seems to be using namespaces, try something like this on your actual xml:

//*[local-name()='div'][@class='b0kes']//*[local-name()='a']/@href

and see if it works.