Remove links to other documents, but text/number to remain

[Goddess]

Hi,

I have a spreadsheet with

1) links to other tabs - example: ='General Info'!$E$9
2) links to external documents - example: ='Sign in to your Microsoft account

Is there a way to remove the external links (item 2) while retaining the text or number? I tried to copy and paste number only; it worked if I manage to identify where the external links are located. If I copy and paste all, the links to the other tabs (item 1) will go missing too.

Thanks!

 

[ranman256]

adjust as you need since this scans column A.

Sub RemoveSomeLinks()
Dim vLnk
Dim iCt As Integer

On Error GoTo errScan

 'scan col. A only.
Range("A1").Select

While ActiveCell.Value <> ""
    iCt = Selection.Hyperlinks.Count
    If iCt = 0 Then
        vLnk = ""
    Else
        vLnk = Selection.Hyperlinks(1).SubAddress
    End If
    
    
    Select Case True
        Case iCt = 0
            'no link
           Debug.Print ActiveCell.Address, "no link"
        Case InStr(vLnk, "!") > 0
             'keep cell link
           Debug.Print ActiveCell.Address, vLnk
        Case Else
             'delete external link
           Selection.Hyperlinks.Delete
    End Select

    ActiveCell.Offset(1, 0).Select 'next row
Wend
Exit Sub
errScan:
If Err = 450 Then
    vLnk = ""
    Resume Next
Else
MsgBox Err.Description, , Err
End If
End Sub

 

[Goddess]

wahhhhh... very complicated to me..

But thanks!