Extract text strings

**DonkeyOte** · 07-16-2010, 06:54 PM

Post some example strings (better yet a file with strings within).

The key will be whether or not other text appears within the string(s) which is to be ignored / irrelevant

In the most general of terms you can use the Split Function to separate the string (populate a Variant array) based on the hyperlink tag.
However, if there is surplus text to be accounted for then the Split approach will most likely require further adaptation.

Without examples it's hard to give anything more specific I'm afraid.

**Paul** · 07-16-2010, 06:56 PM

Hi Wazing, welcome to the forum.

Perhaps this code will help you out. It loops through the cells in column A on Sheet1 and creates a list of the values following the href tags into column A on Sheet2.

Sub extract()
Dim delim As String, nextRow As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, tmpArr As Variant

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
delim = "href="""
nextRow = Sheets("Sheet2").Range("A" & ws2.Rows.Count).End(xlUp).Row + 1

For i = 1 To Sheets("Sheet1").Range("A" & ws1.Rows.Count).End(xlUp).Row
    tmpArr = Split(Cells(i, 1).Value, delim)
    For j = 1 To UBound(tmpArr)
        tmpArr(j - 1) = Left(tmpArr(j), InStr(1, tmpArr(j), """") - 1)
    Next j
    ws2.Range("A" & nextRow).Resize(UBound(tmpArr), 1).Value = Application.Transpose(tmpArr)
    nextRow = nextRow + UBound(tmpArr)
Next i
End Sub

May need some tweaking, but I don't have any sample data to work with...

**snb** · 07-17-2010, 07:33 AM

sub snb()
  sq=sheets(1).usedrange.columns(1)
  for j=1 to ubound(sq)
    c01 =c01 & "|" & join(filter(split(sq(j,1),chr(34)),"HREF"),"|")
  next
  sheets(1).cells(1,1) resize(ubound(filter(split(c01,"|"),"HREF")))=application.transpose(filter(split(c01,"|"),"HREF"))
End Sub

**wazing** · 07-17-2010, 11:16 AM

Paul....Thanks for the code. It did it flawlessly! I really appreciate the help!

**wazing** · 07-20-2010, 08:22 PM

Hey Paul, quick followup question: One variable I had not included.....in my source worksheet1 column (b) has the sourcepage url for the html in column (A) (could be different source url for each cell). Is there a quick addition to the code that will write this value to the column next to each href link from that cell in worksheet2? This would be a huge help in extracting the data....

Thanks!

**Paul** · 07-21-2010, 03:37 PM

Can you provide a worksheet with some sample data that I can test on? Doesn't sound difficult, but want to make sure I get it right the first time.

**wazing** · 07-22-2010, 12:24 AM

Here is some "sample" data in the same format to test on......Thanks!

**wazing** · 07-22-2010, 11:33 AM

You may notice that in each raw html cell there are typically duplicates of each href link....and in cells from the same source URL in column 2, I have to manually remove duplicates. So typically we have to take the output file (which may be thousands of lines long) and sort by column 2, then take that html and remove the href links/remove duplicates for all the href links from the same source url, then copy the source url to the final list from that link and put it in the final worksheet for analysis. It would save a multiple steps if it removed duplicates where column 2 is the same as well....not sure if that is possible but thought I would ask. Maybe it is a separate script I run on the results where it looks for duplicates where they have the same source url in column 2......Thanks!

**snb** · 07-22-2010, 12:01 PM

I can't detect a connection between herefs in column A and URL in column B.

Based on your example: all the unique hreff in column A
from cell A20 downward.

Sub unique_hrefs_snb()
  sq = Cells(1, 1).CurrentRegion
  For j = 1 To UBound(sq)
    sn = Filter(Split(Replace(sq(j, 1), ">", " ")), "href")
    For jj = 0 To UBound(sn)
       If InStr("|" & c01 & "|", "|" & sn(jj) & "|") = 0 Then c01 = c01 & "|" & sn(jj)
    Next
  Next
  sq = Split(c01, "|")
  Cells(20, 1).Resize(UBound(sq)) = Application.Transpose(sq)
End Sub

Extract text strings

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Macro/VBS to extract text strings...please help!

Re: Macro/VBS to extract text strings...please help!

Re: Extract text strings

Re: Macro/VBS to extract text strings...please help!

Re: Macro/VBS to extract text strings...please help!

Re: Macro/VBS to extract text strings...please help!

Re: Macro/VBS to extract text strings...please help!

Re: Macro/VBS to extract text strings...please help!

Re: Extract text strings

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions