URL display in Excel 1

[MarkBeck]

Hi

I have HYPERLINKs in column B in excel. I would like column D to display the full and actual URL path. How do i do this? I believe i have already done this some time ago with a FUNCTION, but cant remember or recreate that.

 

[Bandenjamin]

Try going in the reverse order. Put the URL in box one then refer to it in the hyperlink.

A1=[URL unfurl="true"]http://www.google.com[/URL]
A2=HYPERLINK(A1,Google)

Then if you make a change to the URL it will automatically fix the link.

Hope that helps.

Dan

Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well preserved body. But, rather to skid in sideways, chocolate in one hand, martini in the other, body thoroughly used up, totally worn out and screaming ~ WOO HOO what a ride!

 

[MarkBeck]

Thanks Dan

I guess i didnt explain my question properly.

I have a url like this "

http://www.google.com"

But the full url hiding behind it is more like "

http://www.google.com/bla/bla/bla/etc".

What i need is to "extract" the FULL URL from the TEXT "

http://www.google.com".

 

[RodP]

Hi Mark,

I've quite a few different bits of code you could try depending on how your hyperlink is displayed (eg some hyperlinks are objects)

The first one is for objects that have been copied and pasted from a webpage and have hyperlinks linked to them. It may also work for your hyperlinks but I'm not sure. Give it a go anyway. Alternatively, try the others (see below).

Good luck

RodP

Sub Hyperlink-2-txt()

Dim result
Dim shp As Shape

result = MsgBox("Warning, this will overwrite anything in the adjacent column for each hyperlink in the active worksheet", vbOKCancel)
If result = vbCancel Then Exit Sub

For Each shp In ActiveSheet.Shapes
    'Here we write the address of the hyperlink (the
    'URL) to the cell that is one column to the right
    'of the cell that the picture's bottom right
    'corner touches.  If there's something in that cell
    'it will be overwritten, so this line may need to
    'be adjusted to make sure we're writing to a blank
    'cell
    shp.BottomRightCell.Offset(0, 1).Value = shp.Hyperlink.Address
'This tells the For Next loop to go to the next shape
Next shp

End Sub

Or try this:

Sub ConvertHyperlinks()
    'David McRitchie, misc, 2000-01-17, misc
    '[URL unfurl="true"]http://www.mvps.org/dmcritchie/excel/buildtoc.htm[/URL]
    Dim cell As Range
    Dim hyperlinkaddress As String, hyperlinkformula As String
    For Each cell In Selection
      On Error GoTo skipHyper
      hyperlinkaddress = cell.Hyperlinks(1).Address
      On Error GoTo 0
      If hyperlinkaddress = "" Then GoTo skipHyper
      hyperlinkformula = cell.Formula
      If Left(hyperlinkformula, 1) = "=" Then
        hyperlinkformula = Right(hyperlinkformula, Len(hyperlinkformula) - 1)
      Else
        hyperlinkformula = Replace(hyperlinkformula, """", """""")
        hyperlinkformula = """" & hyperlinkformula & """"
      End If
      cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
        """," & hyperlinkformula & ")"
skipHyper:  On Error GoTo 0
    Next cell
    On Error GoTo 0
    Selection.Hyperlinks.Delete
    For Each cell In Selection
      cell.Formula = cell.Formula
    Next cell
End Sub

Or try this

Function hyperlinkaddress(cell)
    On Error Resume Next
    hyperlinkaddress = cell.Hyperlinks(1).Address
    If hyperlinkaddress = 0 Then hyperlinkaddress = "no link"
End Function

Or try this

Function HyperlinkScreenTip(cell)
    On Error Resume Next
    HyperlinkScreenTip = cell.Shapes(1).Hyperlinks(1).ScreenTip
    If HyperlinkScreenTip = 0 Then HyperlinkScreenTip = "no link"
End Function

Or try this

Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function