Excel: Extract hyperlink from link

: Joel Lipman; Excel; 06 November 2014; Hits: 2711

What?
A quick article on if you want to extract the link from some text you have copied off the web and into an MS Excel document.

How?

Open up a new workbook.
Get into VBA (Press Alt+F11)
Insert a new module (Insert > Module)
Copy and Paste the Excel user defined function below
copyrawstyler
```
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
```
1. Sub ExtractHL()
2. Dim HL As Hyperlink
3. For Each HL In ActiveSheet.Hyperlinks
4. HL.Range.Offset(0, 1).Value = HL.Address
5. Next
6. End Sub
Press F5 and click “Run”
Get out of VBA (Press Alt+Q)

Category: Excel :: Article: 595

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

MS Excel - Open CSV with Long Numbers (3094) Joel Lipman (Excel)
What? This is a quick reminder for myself on opening a CSV in MS Excel but without converting long numbers into...
Crystal Reports: Exporting to Excel omits column headers (18144) Joel Lipman (Excel)
If you've been to the point where you're exporting a report to Excel, and only some of the column headers appear, then try...
Blank columns issue when exporting to Excel (Data Only) from Crystal Reports (49848) Joel Lipman (Excel)
Following up on my article on correcting disappearing headers, a further issue with our web-report is that even an export to...
Excel: Find values in one column that are not in another (19062) Joel Lipman (Excel)
What? A quick note on how to compare two columns for values that are not found in another. I have a column with old...
Excel - Check a column for values found in another column (2796) Joel Lipman (Excel)
What? This is an article to remind me how to search a column in an Excel file for values found in another column (in...
Excel: convert degrees minutes seconds to decimal (4909) Joel Lipman (Excel)
What? A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112. How? Let's pretend all...
Import Excel CSV file as JavaScript array (2834) Joel Lipman (Excel)
What I have: A CSV file exported from Excel along with double-quotes label1,label2 item1a,item2a item1c,"item2c,c" item1b,item2b What I want: To read the file...