How to trim the parsed string on the fly?

[shahin]

I've written some code in vba using IE to collect some information from a webpage. My macro is working awesome. As the result contains mailing address, so the the string is long and separated by space. However, the result I'm getting has got huge gap between each space. After getting the result if i use Trim function ,it solves the issue. My question is whether it is possible to Trim or clean or whatever to get the result as regular string not after the macro is done, i meant on the fly.

Here is the macro:

Sub Get_desc()

    Dim IE As New InternetExplorer, html As HTMLDocument
    Dim post As Object, posts As Object

    With IE
        .Visible = False
        .navigate "http://propaccess.traviscad.org/clientdb/?cid=1"
        Do Until .readyState = READYSTATE_COMPLETE: Loop
        Set html = .document
    End With
  
    html.getElementById("propertySearchOptions_searchText").Value = "9445 Singing Quail"
    html.getElementById("propertySearchOptions_search").Click
  
    Application.Wait (Now + TimeValue("0:00:05"))
  
    For Each post In html.getElementsByTagName("a")
        If InStr(post.href, "Property.aspx?prop_id") > 0 Then
            post.Click                               ''chose an alternative to initiate a click on the link
            Exit For
        End If
    Next post
  
    Application.Wait (Now + TimeValue("0:00:05"))
      
    For Each posts In html.getElementsByTagName("td")
        If InStr(posts.innerText, "Mailing Address:") > 0 Then
            Range("A1") = posts.NextSibling.innerText   '' it gives me result with huge space in between
            Exit For
        End If
    Next posts
  
    IE.Quit
End Sub

Btw, is it possible to get the "Mailing Address" by shaking off "for loop" block? I meant "if function" can stay but "for loop" looks weird when it comes to parse more than 5 fields. Because, for each field if i use individual "for loop" then the code doesn't look good and the process becomes cumbersome. FYI, there is no alternative (to me) to parse the string easily without the way i did up there

 

[NARAYANK991]

Hi ,

Try this :

Range("A1") = Trim(Application.WorksheetFunction.Clean(posts.NextSibling.innerText))

Narayan

 

[shahin]

Ah!!! It solves the problem like magic. I have been a great fan of you Narayan. Thankssss a lot .