Copying Hyper link from Column A to Column B

[pet]

Hi I have a list of text (with hyperlink to web page) down the cloumn A and i was hoping to extract Just the URL into Column B.
Is this possible?
I have tried to research the VB script and I have found:

Sub GetLinkNameOnly()
Dim Adr As String
Dim NoOfAddresses As Integer
Dim CurrentRow As Integer
Dim x As Integer

x = 1: NoOfAddresses = 0
BeginCount: 'Get no of Addresses in Column "A"
If Cells(1 + x, 1) <> "" Then NoOfAddresses = NoOfAddresses + 1: x = x + 1 _

'Put Address in Column "B"
For x = 1 To NoOfAddresses
Adr = Worksheets(1).Range(Cells(2, 1), Cells(2 + NoOfAddresses, 1)).Hyperlinks(x).Address
Cells(1 + x, 2) = Adr
Next
MsgBox Application.UserName & " it's done now", vbInformation
End Sub

This script however only brings the URL for Cell "B2"

Can someone help me why this is the case and how to change the script so it'll so all of my URLs in my list?

I have approx 500 links to extract.

Thank you

 

[Tom Urtis]

Hi I have a list of text (with hyperlink to web page) down the cloumn A and i was hoping to extract Just the URL into Column B.

Is this what you want?

Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Columns(1).SpecialCells(2)
On Error Resume Next
cell.Offset(0, 1).value = cell.Hyperlinks(1).Address
Err.clear
Next
Application.ScreenUpdating = True
End Sub

 

[kmillen]

How about match and mid?

I don't know the exact format of your data, but suppose column A looked something like this. These are not real sites.

text 123 http://site1
text 345873948753 http://site2
text http://site3
text jh2943785y29weihhr92 http://site4
text woie8 http://site5

Column B then could contain a formula like this.
=MID(A1, 1, FIND("http://", A1, 1) - 2)

and column C could contain this formula.
=MID(A1, FIND("http://", A1, 1), LEN(A1))

Copy the 500 or so entries in columns B and C, paste values to the same location and delete column A. Your data is parsed.

Hope this helps.

 

[pet]

Thank you All..
it works Perfectly~!!

really appriciate your help~!!

Thanks again~!!