Split HyperLink

[Just D]

I got the Excel table imported from some Internet Web page, the second
column represents the URL values. There is a need to get the real URL from
the cells of this column and copy it as a visible string to another
cell/column. How can we get the URL from the cell, split it and copy as a
string to another cell, is there a simple way of doing that? If I edit the
cell it shows the HyperLink.

I tried to record a macro editing the cell, selecting the URL and then
copying it as a string to the target cell, but the macro was not recorded
correctly.

Thanks,
Just D.

 

[Mike H]

Hi

You could use this UDF.
Alt+F11 to open VB editor
Right click 'This Workbook'
Insert Module
Paste this in on the right

Function GetLink(HyperlinkCell As Range)
GetLink = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

Enter the formula
=GetLink(A1) in a cell to return the underlying address of a hyperlink in
that cell

Mike