Extract text strings

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...

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.

Hi Wazing, I think this amended code does what you're after.

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

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

For i = 1 To ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    tmpArr = Split(Cells(i, 1).Value, delim)
    tmpStr = Cells(i, 1).Offset(0, 1).Value
    For j = 1 To UBound(tmpArr)
        tmpArr(j - 1) = Left(tmpArr(j), InStr(1, tmpArr(j), """") - 1)
    Next j
    With ws2.Range("A" & nextRow).Resize(UBound(tmpArr), 1)
        .Value = Application.Transpose(tmpArr)
        .Offset(0, 1).Value = tmpStr
    End With
    nextRow = nextRow + UBound(tmpArr)
Next i
ws2.Range("$A:$B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End Sub

Originally Posted by snb

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

Well, the data has been obviously altered to keep it confidential....but a great way to get an example of what I am referring to is to take the sample text and repeat it several times (so you have triple the rows...with the data repeating). I'll give this code a run...thanks for the help! I'll post a response shortly....