Marcro or formula to extract urls from a bunch of text in a column of cells

[dominicp]

I have a column of cells over 2000 rows that have a bunch of text in each cell. In the middle of this text in each cell are websites (not hyperlinked). I was wondering if someone wrote a macro or formula to extract URLs located in a bunch of text? I can parse this out but doing some delimiting but that will take longer.

 

[wigi]

Then it will be Columns(7).

 

[dominicp]

sorry to be dense about this.

i tried the code and got an error.

my data starts on cell g2 and end in g2800.

Will this code check each cell in the column or do i need to modify?

in the code i did the folowing

Sub fff()
For Each r In Columns(7).SpecialCells(2, 2)
r.Offset(, 1) = "www" & Split(Split([g2], "www")(1))(0)
Next
End Sub


but it seems to only take the URL from cell G2 and put it next to all the cells

 

[wigi]

My typo w.r.t. that one:

Sub fff()
    For Each r In Columns(1).SpecialCells(2, 2)
        r.Offset(, 1) = "www" & Split(Split(r, "www")(1))(0)
    Next
End Sub

 

[dominicp]

so i put this in and got an error subscript out of range.

i changed the code so that it was column 7..

anything else i need to do?

my Data starts of G2 and ends on G2008.

 

[dominicp]

wait its probably blowing up if it hits a cell that doesn't have a url...

i tried to clean these out but they might exist in my data set.

anyway to have it just skip that cell and go to next

 

[wigi]

Like this:

Sub fff()
    For Each r In Columns(7).SpecialCells(2, 2)
        If InStr(r, "www") Then r.Offset(, 1) = "www" & Split(Split(r, "www")(1))(0)
    Next
End Sub

Wigi

 

[dominicp]

thanks.