combine multiple results into single cell and trim text results

[kboy1289]

hello all,
i currently have a macro set to open a webpage, search and return data to a specific cell and add the next result in the next row and then format each result to trim out the first 16 characters, what i'm hoping to do is return the results all in the same cell with each result having been trimmed and then separated by a comma and space. the table below shows what the final product should look like (the BW1 Trim Column), also posted the relevant code i have so far.

BW 1 Results	BW 1 (Trim)
Reg Date: 09/26/2005	2005, 2007, 2009
Reg Date: 10/26/2007
Reg Date: 11/26/2009

<tbody>
</tbody>

Set TDelements = HTMLDoc.getElementsByTagName("TD")         
          For Each TDelement In TDelements
          If TDelement.Width = "198" Then
            Sheet1.Range("H2").Offset(r, 0).Value = Trim(TDelement.innerText)
         r = r + 1
         End If
         Next
    
         For Each cell In Range("H:H")
            If Left(cell, 1) = "R" Then
            cell.Value = Right(cell, Len(cell.Value) - 16)
            End If
            Next

 

[miss_ell]

Hello,

I know you're dealing with macros, but perhaps this formula will help you with your code:

=CONCATENATE(RIGHT(A2,4),", ",RIGHT(A3,4),", ",RIGHT(A4,4))

 

[kboy1289]

miss ell,
i did try adding that as a formula to the worksheet however the results are variable in terms of the number of results for each search so they kinda have to be returned all on one cell and trimmed that way. it's doing a search based off data in the same row so the results as of now are populating in other rows data, if that makes sense. thanks for the help!!

 

[miss_ell]

Hello,

This is not fully tested, but hopefully it will give you a start, especially if you know the maximum results you are likely to have:-

=SUBSTITUTE(CONCATENATE(RIGHT(A2,4),", ",RIGHT(A3,4),", ",RIGHT(A4,4),", ",RIGHT(A5,4),", ",RIGHT(A6,4),", ",RIGHT(A7,4),", ",RIGHT(A8,4)),", ,","")

 

[kboy1289]

i wish i knew what the max # of results are, but there is no set number. and this would work if all the data was returned to one cell instead of one cell in the next row for each result, thats where its interefering & thats where i'm having trouble. i'm attempting to have all the data return to one cell, and then formatted. i appreciate all the help!

 

[kboy1289]

bump, anyone?