Extract links from cells of an entire column

[djgenesis13]

Hello guys,

I have a column with cells containing html code with various tags, text and various links. Somewhere in these cells there are some hyperlinks of various length from a specific domains

The format of the urls are http://www then followed by a random number, then a specific domain and then the rst of the url

for example:

<a href="http://www26.dddeeeccc.com/g/sdgsadg/1.html">
<a href="http://www45.dddeeeccc.com/h/zxcvasga/2.html">
<a href="http://www31.dddeeeccc.com/e/eqrgxzv/3.html">
<a href="http://www2.dddeeeccc.com/i/34tasdf/4.html">
<a href="http://www65.dddeeeccc.com/d/arhtk/5.html">
<a href="http://www78.dddeeeccc.com/a/hgjkvbcn/6.html">

as you can see, only "dddeeeccc.com" ​remains the same.

so lets say that in one cell I have

<div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: center;"></div><div dir="ltr" trbidi="on"><div class="separator" style="clear: both; text-align: center;"><img src="http://i.dsdfsf.com/0yGCYO3.jpg" height="355" width="728" /></div><br /><div class="separator" style="clear: both; text-align: center;"><span class="Apple-style-span" style="color: yellow; font-family: 'Helvetica Neue', Arial, Helvetica, sans-serif;"><b>asfjhiuqrtjjxcnvjhuiheugahiandsfkjgvnak;kjgn;kdfngqk;ej;agh;adjng!</b></span></div><div class="separator" style="clear: both;"></div><a name='more'></a><br /><!-- Adsense --><br /><img border="0" height="120" src="https://lh3.sdfsddfsdfsdfsf.com/8CWv9-SpK5xLIcdrAuVnhQ18T8U8fXlkyzfwftFpUTJ9kmbc7O9qoNkRjaPKdbKDPg=w300" width="120" /><br /><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><b style="color: white;"><u>Notific</u></b><span class="Apple-style-span" style="color: lime; font-size: xx-small;"><a href="https://sdfsdf.sdfsdfsdfs.com/sdfsdf/apsdfsdsdfps/details?id"><a href="http://www26.dddeeeccc.com/v/ySXClqAC/1.html"><div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: center;"></div><div dir="ltr" trbidi="on"><div class="separator" style="clear: both; text-align: center;"><img src="http://i.dsdfsf.com/0yGCYO3.jpg" height="355" width="728" /></div><br /><div class="separator" style="clear: both; text-align: center;"><span class="Apple-style-span" style="color: yellow; font-family: 'Helvetica Neue', Arial, Helvetica, sans-serif;"><b>piuahfgpiashahggfaihgipahgipahgui</b></span></div><div class="separator" style="clear: both;"></div><a name='more'></a><br /><!-- Adsense --><br /><img border="0" height="120" src="https://lh3.sdfsddfsdfsdfsf.com/8CWv9-SpK5xLIcdrAuVnhQ18T8U8fXlkyzfwftFpUTJ9kmbc7O9qoNkRjaPKdbKDPg=w300" width="120" /><br /><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><b style="color: white;"><u>Notific</u></b><span class="Apple-style-span" style="color: lime; font-size: xx-small;"><a href="https://sdfsdf.sdfsdfsdfs.com/sdfsdf/apsdfsdsdfps/details?id">

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]

[URL="https://sdfsdf.sdfsdfsdfs.com/sdfsdf/apsdfsdsdfps/details?id"]I want to extract this only:

​

​

http://www26.dddeeeccc.com/v/ySXClqAC/1.html
​

​

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks[/URL]

​

​

 

[portews]

Assuming that there is only one occurrence of "html" in the string:

Sub findString()
'http://www.mrexcel.com/forum/excel-questions/893590-extract-links-cells-entire-column.html
Dim TestCell As Range
Dim MatchPoint As Long
Dim FrontPoint As Long


For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row      'find the last row in column A by looking from the end up
    TestCell = Cells(i, 1)
    If (Len(TestCell) - Len(Replace(TestCell, "html", ""))) / 4 = 1 Then     'if there is only one instance of "html" then
        MatchPoint = WorksheetFunction.Find("html", TestCell) + 4           'find where in shte string it occurs
        'search backwards in the string from where you find the "html" until you find "http"
        FrontPoint = InStrRev(TestCell, "http", MatchPoint - 4, vbTextCompare)  
        'return a string starting at the beginning of "http" ending at "html" and paste it in the next cell to the right
        Cells(i, 2) = Mid(TestCell, FrontPoint, MatchPoint - FrontPoint)       
    End If
Next
End Sub

 

[djgenesis13]

Assuming that there is only one occurrence of "html" in the string:

Sub findString()
'http://www.mrexcel.com/forum/excel-questions/893590-extract-links-cells-entire-column.html
Dim TestCell As Range
Dim MatchPoint As Long
Dim FrontPoint As Long


For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row      'find the last row in column A by looking from the end up
    TestCell = Cells(i, 1)
    If (Len(TestCell) - Len(Replace(TestCell, "html", ""))) / 4 = 1 Then     'if there is only one instance of "html" then
        MatchPoint = WorksheetFunction.Find("html", TestCell) + 4           'find where in shte string it occurs
        'search backwards in the string from where you find the "html" until you find "http"
        FrontPoint = InStrRev(TestCell, "http", MatchPoint - 4, vbTextCompare)  
        'return a string starting at the beginning of "http" ending at "html" and paste it in the next cell to the right
        Cells(i, 2) = Mid(TestCell, FrontPoint, MatchPoint - FrontPoint)       
    End If
Next
End Sub

Hello portews,

Error 91

As you see in my example you will not find any "html" string. You will only find various other thml tags with text inside them. In addition there are ather http links inside the cell as well. I only need to extract the specific link below (the are of the same domain with the number after www varing.
http://www26.dddeeeccc.com/g/sdgsadg/1.html

hope this could help

 

[FormR]

Here is a possible formula option for you to try, assumes your text is in cell A1.

=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("http://www*.dddeeeccc.com",A1),255),"html","html"&REPT(" ",255)),255))

 

[djgenesis13]

Here is a possible formula option for you to try, assumes your text is in cell A1.

=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("http://www*.dddeeeccc.com",A1),255),"html","html"&REPT(" ",255)),255))

WOW!!!

Was it that easy?

Tried it in all my cells and guess what. It works!

Awesome!

Thanks FormR

 

[FormR]

Great news, thanks for letting us know it worked