Extract text between two points

[freddybme]

I have this data in a cell, I want to extract only the actual link, the data at the start of the string can/will vary as will the number of characters between the start and finish.

I want to extract everything between href=" and &height

"..... href="https://test.test.com/farm&height......"

<a adfa="" href="https://secure.ifbyphone.com/recording_email.php?sid=1104035645406129&height=5af%3C/a%3E%3Cbr%20/%3E%0A%3Cbr%20/%3E%0A%3Cbr%20/%3E%0A%3Cbr%20/%3E%0A%3Ca%20href=" https:="" secure.ifbyphone.com="" recording_email.php?sid="1104035645406129&height=525&wi%3Cbr%20/%3E%0A%3Cbr%20/%3E%0AI%20want%20to%20capture%20everything%20between%20href="" and="" &height

 

[Gary McMaster]

Here's one way using code.

Gary

Public Sub Test()

Dim sText As String
Dim iBeg As Integer
Dim iEnd As Integer

sText = "..... href=https://test.test.com/farm&height......"

iBeg = InStr(1, sText, "href=") + 5
iEnd = InStrRev(sText, "&height")

MsgBox Mid(sText, iBeg, iEnd - iBeg)

End Sub

 

[freddybme]

I'd like to place the result in another cell, so for example that string is in A1, I'd like results to be in A2

Thanks again for the help and fast reply

 

[Gary McMaster]

Try like this.

Gary

Public Sub Test()

Dim iBeg As Integer
Dim iEnd As Integer

iBeg = InStr(1, ActiveSheet.Range("A1"), "href=") + 5
iEnd = InStrRev(ActiveSheet.Range("A1"), "&height")

ActiveSheet.Range("A2") = Mid(ActiveSheet.Range("A1"), iBeg, iEnd - iBeg)

End Sub

 

[Gary McMaster]

Here's another way using "Split" instead of "Instr".

Gary

Public Sub Test()

ActiveSheet.Range("A2") = Split(Split(ActiveSheet.Range("A1"), "href=")(1), "&height")(0)

End Sub

 

[freddybme]

This is working perfect, one final request, I need it to work for a cell range, so A1-A1000 and place the result next to it in column B

 

[Jonmo1]

Try a formula

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("href=",A1)+5,""),"&height",REPT(" ",LEN(A1))),LEN(A1)))

 

[Gary McMaster]

Using "Split":

Public Sub Test1()

Dim oTarget As Range
Dim oCell As Range

Set oTarget = ActiveSheet.Range("A1:A1000")

For Each oCell In oTarget

oCell.Offset(0, 1).Value = Split(Split(oCell.Value, "href=")(1), "&height")(0)

Next oCell

End Sub

Using "Instr" & "InstrRev":

Public Sub Test2()

Dim iBeg As Integer
Dim iEnd As Integer

Dim oTarget As Range
Dim oCell As Range

Set oTarget = ActiveSheet.Range("A1:A1000")

For Each oCell In oTarget

    iBeg = InStr(1, oCell.Text, "href=") + 5
    iEnd = InStrRev(oCell.Text, "&height")
    
    oCell.Offset(0, 1).Value = Mid(oCell.Text, iBeg, iEnd - iBeg)

Next oCell

End Sub

Not sure which would be faster. Maybe someone else will be able to provide you with a formula that may be faster still.

PS I did not see jonmo1's formula prior to posting

Gary