How to check if hyperlink exists in a worksheet in Excel?

If we have a huge data where hyperlinks have been scattered throughout the worksheet, then it becomes a tedious task to find the hyperlinks and external references. This tutorial will help a user to find the hyperlinks available in a worksheet in the following scenarios −

• Find all the hyperlinks in Excel

• Find all the hyperlinks linked to a specific text

• Find all hyperlink locations with VBA Code

Find All Hyperlinks in Excel

Step 1 − A sample worksheet has been shown below with scattered hyperlinks.

Step 2 − Please note that, using Find and Replace feature, all hyperlinks can be identified easily. For this, press Ctrl+H on the keyboard. The find and replace dialog box will open.

Or you can open the same by following the below path −

Home > Editing > Find & Select > Replace

Step 3 − On Find & Replace dialog box, click Options.

Step 4− The dialog box will expand as following. Go to Format against Find what and click the down arrow to select Choose Format from Cell.

Step 5 − Select a cell from the worksheet which has a hyperlink. Then a preview of the cell data will be displayed as following. Now click Find All button at the bottom of dialog box.

Step 6 − It will display all the hyperlinked cells in a list as shown below. You may select each cell individually or hold the control key to select multiple cells from the list.

Find All Hyperlinks Linked to SpecificText in Excel

Step 1 − A sample worksheet has been shown with some similar hyperlinked cell values.

Step 2 − Repeat the step 2 and 3 from the above method.

Step 3 − Go to Format against Find what and click the down arrow to select Choose Format from Cell. Now select a cell for which specific text hyperlink to be searched. Next, in the Find what field enter the exact cell value that you want to find.

Step 4 − Now click Find All button at the bottom of dialog box. It will display all the hyperlinked cells with the entered value in a list as shown below. You may select each cell individually or hold the control key to select multiple cells from the list.

Find all hyperlink locations with VBA Code

Step 1 − Press Alt+F11 keys on the keyboard and this will open the window of Microsoft Visual Basic for applications.

Step 2 − In the Microsoft Visual Basic for applications window, go to Insert > Module.

Step 3 − Now paste the following code in the workspace as following −

Code Snippet

Sub HyperlinkCells() \ A VBA function used to jump to a location of a worksheet where hyperlinks are available
   Dim xAdd As String \ Adding a variable xAdd as string type
   Dim xTxt As String \ Adding a variable as xText as string type
   Dim xCell As Range \ Adding a variable xCell as Range type
   Dim xRg As Range \ Adding a variable as xRg as range type.
   On Error Resume Next \ when a run-time error occurs, go to the statement immediately following the statement where the error occurred and execute next.
   xTxt = ActiveWindow.RangeSelection.AddressLocal \ Returns a Range object that represents the selected cells on the worksheet in the active window
   Set xRg = Application.InputBox("Please select range:", "Kutools for Excel", xTxt, , , , , 8) \ A popup message box to display range of cells
   If xRg Is Nothing Then Exit Sub \ If no hyperlink is found then exit from the sub statement
   For Each xCell In xRg \ Condition For each xCell variable in the range
      If xCell.Hyperlinks.Count > 0 Then xAdd = xAdd & xCell.AddressLocal & ", " \ Condition to count the hyperlinked cells in the active sheet
      Next
      If xAdd <> "" Then \ If the value of xAdd is Not Equal to firstAddress then execute the next statement.
         MsgBox "Hyperlink existing in the following cells: " & vbCrLf & vbCrLf & Left(xAdd, Len(xAdd) - 1), vbInformation, "Kutools for Excel" \ Display a msg box with all hyperlinked cell addresses.)
      End If \ end if condition
End Sub \ end sub statement

Step 4 − Now, press F5 to run the code. A dialog box will open as Kutools for Excel.

Step 5 − Now select the range of dataset in which you want to search the hyperlinks and click OK button.

Step 6 − Then a dialog box will open displaying the cell locations having hyperlinks.

Conclusion

Hence in this article we have learned three methods to find hyperlinks in an excel sheet. Finding hyperlinks in a huge data is a tedious task. Sometimes we need to consolidate the same for reference purpose or data sharing. The VBA code method will actually locate all the hidden hyperlinks also placed without any specific formatting.

Hope this article helped you to learn a new technique of excel. Keep exploring and keep learning.