How to extract URLs from hyperlinks in excel workbook with formula

1. Open Excel and paste your hyperlinks, for example, I’ll scrape links from this list with LinkedIn’s links written as “View on LinkedIn” but containing different URLs in each row.

2. Go to File — Options — Customize Ribbon — Developer and mark Developer field

3. Open Developer Tab and click Visual Basic

4. Click Insert — Module. Copy and paste this code in the new window

Public Function ShowAddress(rng As Range) As String
If rng.Cells.Count > 1 Then
ShowAddress = CVErr(xlErrValue)
Else
ShowAddress = rng.Hyperlinks.Item(1).Address
End If
End Function

5. Save the file as Excel Macro-Enabled Workbook (*.xlsm)

6. Go to your project and use the new formula

=ShowAdress(Cell address)

The formula will work only in this Workbook.