Find cells that contain links to unavailable sources

The following VBA macro can help list all linked source workbooks in a new worksheet of current workbook.
(credit: https://www.extendoffice.com/documen...all-links.html)

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA: List all links in Excel.

Sub ListLinks()
    Dim xSheet As Worksheet
    Dim xRg As Range
    Dim xCell As Range
    Dim xCount As Long
    Dim xLinkArr() As String
    On Error Resume Next
    For Each xSheet In Worksheets
        Set xRg = xSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        If xRg Is Nothing Then GoTo LblNext
        For Each xCell In xRg
            If InStr(1, xCell.Formula, "[") > 0 Then
                xCount = xCount + 1
                ReDim Preserve xLinkArr(1 To 2, 1 To xCount)
                xLinkArr(1, xCount) = xCell.Address(, , , True)
                xLinkArr(2, xCount) = "'" & xCell.Formula
           End If
        Next
LblNext:
    Next
    If xCount > 0 Then
        Sheets.Add(Sheets(1)).Name = "Link Sheet"
        Range("A1").Resize(, 2).Value = Array("Location", "Reference")
        Range("A2").Resize(UBound(xLinkArr, 2), UBound(xLinkArr, 1)).Value = Application.Transpose(xLinkArr)
        Columns("A:B").AutoFit
    Else
        MsgBox "No links were found within the active workbook.", vbInformation, "KuTools for Excel"
    End If
End Sub

and
(credit: https://www.thespreadsheetguru.com/t...al-links-excel )

This little macro code will go through all the external links in your workbook and break the links. Note that this will not remove those pesky (hard-to-find) external links that may be hiding inside your charts. This code only addresses links that would show up inside the Edit Links dialog box (Data tab > Connections Group > Edit Links).

Sub BreakExternalLinks()
'PURPOSE: Breaks all external links that would show up in Excel's "Edit Links" Dialog Box
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

Dim ExternalLinks As Variant
Dim wb As Workbook
Dim x As Long

Set wb = ActiveWorkbook

'Create an Array of all External Links stored in Workbook
  ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)

'Loop Through each External Link in ActiveWorkbook and Break it
  For x = 1 To UBound(ExternalLinks)
    wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
  Next x

End Sub

If you know the source file name. You can use CTRL + F to find and then replace the reference.

Alternately, you could replace broken link(s) with some dummy file (in easy to find location such as C:\Temp\File.xlsx). Then find and replace.

To search you would use something like 'DriveLetter:\Folder\*' Or if needed additional detail to specify broken link.
Ex: 'C:\Temp\*' using above example when broken links are replaced with dummy file.
Make sure you select look in->Formula.