Excel - Extracting URL from a Hyperlink to Text

[ladyck3]

I need to know if there is a way to toggle a list of hyperlinks to view the actual URLs rather than the hypertext.

For instance (and please tell me what this is as well as its driving me crazy I can't remember) there's a way to toggle a spreadsheet to show all of the formulas in the sheet rather than the data and toggle back to data, its a keystroke combination and I can't remember what it is.

Is there a way to do the same for URLs? Ultimately what I DESPERATELY NEED is a way to take a list of Hyperlinks and (hopefully) simply extract the data so I have to columns of text....

Column 1: The question itself, just text, no link
Column 2: The link that is related to that question (the answer hyperlink).

Can this be done relatively easy? I don't know VBA and I'm certain if its not something I can toggle the view, it will require VBA.

Don't stress over it (spend a lot of time, seriously) I do know how to open the editor and paste a script into it, that's my extensive knowledge about VBA scripting... so be gentle.

I know you guys love a challenge, this may be an easy peasy one for ya I just don't know.

Thanks in advance..
Ladyck3
(Laurie)


LadyCK3
aka: Laurie

 

[CautionMP]

[ol][li]Tools=>Options=>View=>Window Options=>Formulas: This will toggle the formulas on and off, unfortunately it doesn't work for Hyperlinks.[/li][li]This code snipit will allow you to extract the URL from a hyperlink, paste it into a Module and it will become available in the Formula Palette

Public Function HyperlinkText(ByRef CellAddress As Range) As String
   'Since there is only one Hyperlink per cell
   'hard code the first ordinal
   HyperlinkText = CellAddress.Hyperlinks(1).Address
End Function

[/li][/ol]
Hope this helps,
CMP


Instant programmer, just add coffee.

 

[macropod]

Hi Laurie,

The following function finds the address of a source file referred to in a source cell’s hyperlink.

Public Function HLinkAddr(Source As Range)
Dim HAddr As String
Dim HSubAddr As String
HAddr = Replace(Source.Hyperlinks(1).Address, "/", "\")
If Trim(HAddr) = "" Then HAddr = ThisWorkbook.FullName
If InStr(HAddr, "..\") Then HAddr = ThisWorkbook.Path & Replace(HAddr, "..\", "")
If InStr(HAddr, ":") = False Then HAddr = ThisWorkbook.Path & "\" & HAddr
HSubAddr = Source.Hyperlinks(1).SubAddress
If HSubAddr <> "" Then HAddr = HAddr & " - " & HSubAddr
HLinkAddr = HAddr
End Function

This function can be used in a UDF, where the formula is expressed as
=HLinkAddr(A1)
or called via code like:

Dim Source As Range
Dim Called As Boolean

Sub TestIt()
Set Source = Range("A1")
Called = True
MsgBox HLinkAddr(Source)
Called = False
End Sub

NOTE: The ‘Source’ and variable and the ‘Called’ variable have to be made available outside the procedure (i.e. preceding the code) when calling from a subroutine and the following line should be inserted into the function immediately after its ‘Dim’ statements (for error trapping):

If Called = True And Source.Hyperlinks.Count = 0 Then Exit Function

Cheers

 

[ladyck3]

I am so totally lost... I'm sorry and thank you for your time but this is so totally over my head... and its not up to any of you to train me.... I think I'm untrainable when it comes to this stuff.

I do thank you but I've tried this stuff (with my limited knowledge) and have no clue what to do... it is not working for me but I am totally sure its user error (VBA ignorance or stupidity, whichever you choose).



LadyCK3
aka: Laurie

 

[macropod]

Hi Laurie,

In the workbook in which you want the function to work:
1. Open the VBE (Ctrl-F11)
2. If you don't already have a code module attached to the workbook, add one via Insert|Module
3. Activate the module
4. Insert the 'HLinkAddr' function into the module
5. Activate your worksheet
6. Insert the formula: =HLinkAddr(A1), replacing 'A1' with your hyperlink cell's address
7. If the formula works (as it should), close the VBE

Cheers