Excel genius? anyone?

[woopwoop]

I have an excel sheet with a few thousand links in a column.

Does anyone know a quick and easy way to split the anchor text from the url?
So that I have 2 columns...

 

[ratboy]

Just paste into Notepad and do a replace on the code in the middle of the link replacing it with a ", " then replace the code not needed at the front and end of the code with nothing. save and open up in excel.

Not sure if that makes sense, if you get stuck email me.

 

[woopwoop]

Thanks RB - I tried but it was only pasting the anchor text into the text editor so there wasn't anything for me to replace... strange because I use the replace feature a lot.

I did come up with a solution from the web which is a massive help. I'm sure it'll help others here who deal with excel and links... (not too sure how big that % is ! )

You need to add a function/module:

Function HyperLinkText(pRange As Range) As String

Dim ST1 As String
Dim ST2 As String

If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If

ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress

If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
End If

HyperLinkText = ST1

End Function

Then you use this in the cell:

=HyperLinkText(A1)

And you can use it like any other formula and drag the corner down, it'll handle a long list quickly and will make you smile that you didn't have to do this by hand.