CODEWITHSUNDEEP
vbaexcel
Tummy Tum
Tummy Tum

Reputation: 35

Trimming a string as it is retrieved in a for loop?

I have this for loop which retrieves the phone number from Google maps pages of a business.

               For Each a In Array("_Xbe _ZWk kno-fv")
                aa = ""
                aa = IE.Document.getElementsByClassName(a)(0).innerText
                If Len(bb) > 0 Then
                Wksht.Cells(LngRow, 10) = aa
                Exit For
              End If

           Next

It works great BUT the phone number comes in the format of (xx) xxxx xxxx. I want it to be xxxxxxxxxx. How do you trim and concate in the middle of a for loop?

Upvotes: 0

Views: 61

Answers (3)

user6432984
user6432984

Reputation:

You'll need to change the NumberFormat of the Column and use replace to get rid of the parenthesis ans spaces.

Wksht.Columns(10).NumberFormat = "0000000000"

For Each a In Array("_Xbe _ZWk kno-fv")
    aa = ""
    aa = IE.Document.getElementsByClassName(a)(0).innerText
    aa = Replace(aa, "(", "")
    aa = Replace(aa, ")", "")
    aa = Replace(aa, " ", "")

    If Len(bb) > 0 Then
        Wksht.Cells(LngRow, 10) = aa
        Exit For
    End If
Next

Upvotes: 2

Techie
Techie

Reputation: 181

try below code

 For Each a In Array("_Xbe _ZWk kno-fv")
            aa = ""
            aa = IE.Document.getElementsByClassName(a)(0).innerText
            If Len(aa) > 0 Then
            aa =trim(aa)
            aa = mid(aa, 2,2) & mid(aa,6,4)& right(aa,len(aa)-10)

            Wksht.Cells(LngRow, 10) = aa
            Exit For
          End If

       Next

Upvotes: 0

Comintern
Comintern

Reputation: 22195

The obligatory regular expression answer...

For Each a In Array("_Xbe _ZWk kno-fv")
    aa = vbNullString
    aa = IE.Document.getElementsByClassName(a)(0).innerText
    If Len(bb) > 0 Then
        With New RegExp
            .Pattern = "[^\d]"
            .Global = True
            Wksht.Cells(LngRow, 10) = .Replace(aa, vbNullString)
        End With
        Exit For
    End If
Next

Note: This requires a reference to Microsoft VBScript Regular Expressions, or you can use late binding by using With CreateObject("VBScript.RegExp") instead of With New RegExp.

Upvotes: 0

Related Questions