Solved

Excel function to do exactly the opposite of =HYPERLINK()

Posted on 2016-08-09
5
113 Views
Last Modified: 2016-08-18
Hello,

Does Excel have a function which is exactly the opposite of =HYPERLINK()?

For example, using the =HYPERLINK() function, a long list of URLs in an Excel column can easily be converted to a corresponding list of hyperlinks in a different column.

I'm wondering if there is a function which, beginning with a long list of hyperlinks, will produce a corresponding list of URLs.

If not, what is the best workaround?

Thanks
0
Comment
Question by:WeThotUWasAToad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41749878
A sample workbook along with the desired output mocked up manually would be helpful.
0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41749879
Hi,

If the friendly name is the same as the link, you could copy paste the value
if not pls try
Sub Macro()
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    res = ""
    On Error Resume Next
    res = Evaluate(Split(Replace(Replace(c.Formula, "=HYPERLINK(", ""), ")", ""), ",")(0))
    On Error GoTo 0
    If res <> "" Then
        c.Offset(, 1) = res
    End If
Next
End Sub

Open in new window

Regards
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41750115
Copy list of URLs and Paste as Values in new range is what you want I believe
(I think Rgonzo1971 suggests that too)
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41750118
A macro can do that too, the following code will copy the URL from any hyperlink in the spreadsheet to the column NEXT to it :)
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
    HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

Open in new window

I've uploaded an example as well
Hyperlinks.xlsm
0
 

Author Closing Comment

by:WeThotUWasAToad
ID: 41761667
Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question