How to compare hyperlinks

[Sylvia2047]

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?

[#2 · Accepted solution]

@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.

[#3]

@Hans Vogelaar 

Thank you very much! That's great help!