Hyperlinks

Hi @Amon Phiri,

Thank you for posting your question in the Microsoft Q&A forum.

The picture of sheet 2 in your question is having error so I can't clearly understand your request. Can you reshare the screenshot of sheet 2 so I can clearly understand the situation and provide the most appropriate solution to your concern.

Thank you for your cooperation and understanding. Looking forward to hearing from you.

Hi @Amon Phiri,

Thank you for your response.

For your situation, I suggest you can try following this guideline:

1- Using XLOOKUP

If the item code in sheet 1 also exists in sheet 2 (like the example below), you can use XLOOKUP formula to look up and return the content of the Item Code. In cell B1, use this formula and then copy paste into other cells

=XLOOKUP(A1,'Sheet 2'!C4:C8,'Sheet 2'!D4:D8)

2- Using VBA macro

You can use this VBA script to read the hyperlinks target address:

• In your Excel file, press Alt + F11 to open VBA Editor
• Then select Insert > Module and paste this script into it

Option Explicit
Public Function LinkTargetAddress(rng As Range) As String
    On Error GoTo safe_exit
   
    If rng Is Nothing Then GoTo safe_exit
    If rng.Hyperlinks.Count = 0 Then GoTo safe_exit
   
    Dim hl As Hyperlink
    Set hl = rng.Hyperlinks(1)
    If Len(hl.SubAddress) > 0 Then
        LinkTargetAddress = hl.SubAddress
    Else
        LinkTargetAddress = ""
    End If
    Exit Function
   
safe_exit:
    LinkTargetAddress = ""
End Function
 
Public Function LinkTargetValue(rng As Range, Optional colOffset As Long = 0) As Variant
    On Error GoTo safe_exit
   
    If rng Is Nothing Then GoTo safe_exit
    If rng.Hyperlinks.Count = 0 Then GoTo safe_exit
   
    Dim hl As Hyperlink
    Set hl = rng.Hyperlinks(1)
   
    Dim subAddr As String
    subAddr = hl.SubAddress
    If Len(subAddr) = 0 Then GoTo safe_exit
   
    Dim exclPos As Long
    exclPos = InStr(1, subAddr, "!", vbTextCompare)
    If exclPos = 0 Then GoTo safe_exit
   
    Dim rawSheet As String, rawRef As String
    rawSheet = Left(subAddr, exclPos - 1)
    rawRef = Mid(subAddr, exclPos + 1)
   
    If Left(rawSheet, 1) = "'" And Right(rawSheet, 1) = "'" Then
        rawSheet = Mid(rawSheet, 2, Len(rawSheet) - 2)
    End If
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(rawSheet)
   
    Dim tgt As Range
    Set tgt = ws.Range(rawRef)
   
    If colOffset <> 0 Then
        Set tgt = tgt.Offset(0, colOffset)
    End If
   
    LinkTargetValue = tgt.Value
    Exit Function
   
safe_exit:
    LinkTargetValue = ""
End Function

• Close the VBA editor. In the cell next to the item code, use the new function that have been added to extract the hyperlink value: =LinkTargetValue(A1)

Note: If the hyperlink is an external URL, points to different workbook or using HYPERLINK formula to add, the function may not work properly. Please notify me if you're using one of these options and I will provide the appropriate solution for it.

I hope the information provided proves useful. Please proceed with the outlined steps and let me know whether they resolve the issue. If not, I’ll be glad to continue working with you to find a solution.

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out in the comments of this post. I'll be happy to support.

I look forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.