hyperlink address

[mfahadakbar]

Hi guys,

i want to extract hyperlink text from the friendly names that i have

when i use range.hyperlinks(1).address property it only gives my first 40 characters of the hyperlink

I am actually trying to extract last 7 characters from the hyperlink , but since it is not reading more than 40 characters , its not picking up the right text

Any idea why it is so and how do i get the complete hyperlink string ?

Thanks

 

[Special-K99]

Maybe use range.hyperlinks(1).name

This code (amended but not written by me) picks up more than 40 characters

Function HLink(rng As Range) As String
'extract URL from hyperlink - posted by Rick Rothstein
  If rng(1).Hyperlinks.Count Then
      HLink = rng.Hyperlinks(1).Name
  End If
End Function

 

[mfahadakbar]

Thank you,

but i figured its not the length of the url , its about a character in the URL , which is '#' . Where ever this shows up, excel will stop reading url further .
This is where i am stuck

 

[John_w]

The part after the '#' is in the hyperlink's SubAddress property.

    Dim link As Hyperlink
    Set link = Range("A2").Hyperlinks(1)
    MsgBox link.Address & "#" & link.SubAddress

 

[mfahadakbar]

love it John....

this was such a pain for me, and what a simple solution you have

Thank you very much