Extracting Hyperlinks from a Cell in Excel

Objective

To extract hyperlinks embedded in a cell using a formula in Excel.

Formula

Excel does not have a direct built-in function to extract hyperlinks. However, a combination of functions can be employed to achieve this in a relatively manual way. You can create a User Defined Function (UDF) using VBA (Visual Basic for Applications) to facilitate the extraction.

Here’s the process:

Step 1: Create a User Defined Function (UDF)

1. Press ALT + F11 to open the VBA editor.
2. Click on Insert > Module to create a new module.
3. Copy and paste the following code:

Function GetHyperlink(rng As Range) As String
    On Error Resume Next
    GetHyperlink = rng.Hyperlinks(1).Address
    On Error GoTo 0
End Function

Step 2: Use the UDF in Excel

After creating the UDF, you can return to your Excel worksheet and use the following formula:

=GetHyperlink(A1)

In the formula above:

• Replace A1 with the reference to the cell containing the hyperlink.

Explanation of the UDF

• Function Declaration: The function GetHyperlink takes one parameter, rng, which represents the range of the cell you want to extract the hyperlink from.
• On Error Resume Next: This statement allows the function to continue running without stopping when an error occurs (for example, if the cell does not contain a hyperlink).
• Hyperlink Extraction: rng.Hyperlinks(1).Address accesses the first hyperlink in the specified cell and retrieves its address.
• Return Value: The extracted hyperlink is returned as a string.

Practical Example

Scenario

Assume cell A1 contains the text “Click here” with a hyperlink directed to https://www.example.com.

Implementation

1. After adding the UDF to your workbook, enter the following in cell B1:

=GetHyperlink(A1)

2. Cell B1 will display https://www.example.com, effectively extracting the hyperlink from cell A1.

Conclusion

This method provides a robust way to extract hyperlinks from cells in Excel. For further learning and advanced Excel techniques, consider exploring the Enterprise DNA Platform, which offers comprehensive courses and resources tailored to enhancing your data analysis skills.