WeThotUWasAToad
asked on
Excel function to do exactly the opposite of =HYPERLINK()
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
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
A sample workbook along with the desired output mocked up manually would be helpful.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Copy list of URLs and Paste as Values in new range is what you want I believe
(I think Rgonzo1971 suggests that too)
(I think Rgonzo1971 suggests that too)
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 :)
Hyperlinks.xlsm
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
I've uploaded an example as wellHyperlinks.xlsm
ASKER
Thanks