Extract text strings

**wazing** · 07-16-2010, 05:27 PM

I have a very large spreadsheet with html in each cell in column 1. There are multiple text strings (potentially 3 to 20) within the html in each cell. Each string is preceded with HREF= and the string has a " before and after the string.

I want to have a macro that searches each cell in column 1 and finds the HREF= (multiple instances...as few as 3 to as many as 20) and extracts the string between the double quotes (i.e. "string") and writes them to the first column in worksheet 2 in the workbook.

I am not a programmer...but have spent a few days trying to find a macro or use the MID or FIND function to do this with minimal success. Can someone please help?

Thanks!

**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!