Can I use an excel formula to extract the link location of a hyperlink in a cell?

Question

I have a spreadsheet with a large number of cells containing hyperlinks with display text different than the hyperlink locationie:*cell location: A1**display text = "Site Info"**hyperlink location = "http://www.mylocation.com"*Is there an excel formula that allows me to access the text string of the hyperlink location?Ideally it would look like this:**FORMULA(A1) = "http://www.mylocation.com"**

Answer 1

To extract URL from a hyperlink,

Select the hyperlink,

Press SHIFT + F10,

Choose Edit Hyperlink,

Copy the hyperlink from address bar

Press ok or esc

Move to blank cell

Pres CTRL + V

To extract multiple URLs from multiple hyperlinks,

you have to use VBA Code.

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function below

Sub ExtractHL()

Dim HL As Hyperlink

For Each HL In ActiveSheet.Hyperlinks

HL.Range.Offset(0, 1).Value = HL.Address

Next

End Sub5

5. Press F5 and click “Run”

Get out of VBA (Press Alt+Q)

Happy Learning !!!