Cheryl has a worksheet that contains many hyperlinks. The display text for each hyperlink is different than the target for the hyperlink. These hyperlinks are all in column A. Cheryl would like to leave the display text in column A, move the target URL into column B, and delete the hyperlink in column A. What she needs to end up with is the display text in column A, the URL in column B, and no active hyperlinks in the worksheet.
Processing and extracting information from hyperlinks in this manner requires the use of a macro. The following is an example of a flexible macro that examines whatever hyperlinks are in the selected range of cells. If a hyperlink is found, the URL for the hyperlink is entered to the right of the hyperlink and then the hyperlink itself is deleted. This leaves the display text in the cell where the hyperlink used to be.
Sub GetHLInfo() Dim rRng As Range Dim cell As Range Set rRng = ActiveSheet.Range(ActiveWindow.Selection.Address) For Each cell In rRng If cell.Hyperlinks.Count > 0 Then cell.Offset(0, 1) = cell.Hyperlinks(1).Address cell.Hyperlinks(1).Delete End If Next End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9898) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Extracting Hyperlink Information.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Hyperlinks can be a great timesaver and very convenient. Unless, of course, if they don't work as you expect. This tip ...
Discover MoreCreating a drop-down list with Excel's data validation feature can be a nice touch for a worksheet. What if you want the ...
Discover MoreWhen you add a hyperlink to a worksheet, over time and after doing a bunch of editing, what you see in the cell can get ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-03-19 13:19:37
SandyLee Griswold
Thank you so much for this macro. You saved me HOURS of time this morning. You're my HERO!
2014-08-11 12:10:55
Bryan
The first working line of the macro is really clumsy... instead of
Set rRng = ActiveSheet.Range(ActiveWindow.Selection.Address)
Why not
Set rRng = Selection
Or better yet, just skip the extra object and use Selection directly within the loop. Usually I'm not a fan of the Select object, but in this case it's how you are choosing what cells to run the macro over so it's the most logical choice.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments