Forum Discussion

Sylvia2047's avatar
Sylvia2047
Copper Contributor
Aug 29, 2022
Solved

How to compare hyperlinks

Hi all,

I would like to pick your brains on how I could compare hyperlinks in 2 different cells.  The links that I want to compare are embedded links, i.e. the cell displays a set of texts (e.g. Company A) and the texts are hyperlinked to a site (e.g. the website of Company A).  What I want to do is to find any duplicate links in a column of cells with hyperlinked texts. 

I have been trying conditional formatting to find duplicate cells, but it seems I can only compare the texts in the cells but not the links embedded to the texts?

Any idea?

excel
  • HansVogelaar's avatar
    HansVogelaar
    Aug 29, 2022

    Sylvia2047 

    Create the following custom function in a module in the Visual Basic Editor:

    Function HyperlinkAddress(cell As Range) As String
    On Error Resume Next
    HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

    Let's say you have hyperlinks in A2 and down.

    Enter the following formula in B2, then fill down:

    =HyperlinkAddress(A2)

    You can then highlight duplicates in column B.

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 29, 2022

    Sylvia2047 

    Create the following custom function in a module in the Visual Basic Editor:

    Function HyperlinkAddress(cell As Range) As String
    On Error Resume Next
    HyperlinkAddress = cell.Hyperlinks(1).Address
End Function

    Let's say you have hyperlinks in A2 and down.

    Enter the following formula in B2, then fill down:

    =HyperlinkAddress(A2)

    You can then highlight duplicates in column B.

Resources