Store all external links in an array and then replace all links with an empty string

You have not provided a lot of information.
Do want to save all formula in workbook or just all on one sheet etc?

ActiveSheet.Range("B1").formula   IS HOW YOU OBTAIN FORMULAE DETAILS

But the principle is that you need to loop through the cells holding the formulae that you want to keep, and then save the formula to a 2 dimensional array, together with the cell address (otherwise useless!)

The code below saves the formula from B1 to B20 in the active sheet, to array named formArr and also the cell addresses.
The message box shows what is held in the array for each entry
Adapt to suit your particular requirements

Sub a()
Dim formArr(20, 1)
For r = 1 To 20
formArr(r, 1) = ActiveSheet.Range("B" & r).Formula
formArr(r, 0) = ActiveSheet.Range("B" & r).Address(0, 0)
MsgBox formArr(r, 1) & " in cell " & formArr(r, 0)
Next r
End Sub

Right let's try to sort out the rest of your thread next, and then we can pull it all together.
I have tried to work out what we need to strip out from the cells in sheet "Destination" to leave you with the bare formula.
The external link is always wrapped in single quotes ' '
- the macro splits each cell every time it encounters character '
- dumps that bit of string into an array
- removes the string if it contains a " \ " (which is present in all links)
- joins all the pieces back together again
- removes any !
- writes resultant string to 2 new worksheets
- FormulaSheet should contain what you are looking for
- TextSheet allows you to see the formula without going into individual cells

So test as follows
- create a test copy of your file (just in case!!)
- copy the VBA below into Module1
- run it and check to see if it is what you are looking for

Sub ClearLinks()
If Evaluate("ISREF('FormulaSheet'!A1)") Then Sheets("FormulaSheet").Cells.ClearContents Else Sheets.Add.Name = "FormulaSheet"
If Evaluate("ISREF('TextSheet'!A1)") Then Sheets("TextSheet").Cells.ClearContents Else Sheets.Add.Name = "TextSheet"

Dim wsT As Worksheet, wsF As Worksheet, wsD As Worksheet
Set wsT = Sheets("TextSheet")
Set wsF = Sheets("FormulaSheet")
Set wsD = Sheets("DestinationSheet")

Dim text As String, strFormula As String
Dim formArr As Variant
Dim a As Long, i As Long
   
   For Each cell In wsD.UsedRange.Cells
 'split the text for this cell
        text = cell.Formula
        formArr = Split(text, "'")

'create new formula string
        For a = 0 To UBound(formArr)
            If InStr(CStr(formArr(a)), "\") > 0 Then
                formArr(a) = ""
            Else
            End If
                strFormula = strFormula & CStr(formArr(a))
        Next a
'put in equivalent cell on new sheet
    strFormula = Replace(strFormula, "!", "")
    cellAddress = cell.Address(0, 0)
    wsT.Range(cell.Address(0, 0)) = "F: " & strFormula
    wsF.Range(cell.Address(0, 0)) = Replace(wsT.Range(cell.Address(0, 0)), "F: ", "")
    strFormula = ""
Next

End Sub

Thanks for the workbook - I can now test using your file - much better.
I will add in the sheet name for you and tailor the previous macro later today

Can you check to see if the formulae in the cells all look ok to you.
Is the sub-routine giving you anything you do not like?