Excel: Find and Replace External Reference Values

Question

The true problem I'm trying to solve:

• I've pasted text including formulas from one Excel workbook to another, but I don't want to reference the old workbook in the formulas

The question I'm asking:

• How can I find and replace (or similar results) references to an old workbook, where I want formula to instead reference (identically named) internal data table? I want to replace the values within a formula, but my best guess is that Excel is having a problem with some kind of special character

Sample:

The following is formatted as a data table named "Data" in "MyOldWorkbook.xlsx" and "MyCurrentWorkbook.xlsx"

ColRowNum	ColAmt
1	5
2	10
3	5

• The original/desired formula: =COUNTIFS(Data[ColAmt],"<=10")
• The unwanted formula when pasted: =COUNTIFS('MyOldWorkbook.xlsx'!Data[ColAmt],"<=10")

Notes:

• The sample is overly simplistic. The easiest thing to do is manually just deleted out the unwanted text, but I'm looking for the systematic solution.
• I feel like I'm missing some "escape" character command/grep thing/something in the find & replace dialogue.

Fails

• If I try to put into the Find and Replace dialogue box(es) Find: 'MyOldWorkbook.xlsx'! and replace with "" (actually blank), I get an error.
• If I try to shorten it (remove the !) I get an error about how excel has broken formulas (true) and refuses to find/replace for me
• Can't break the external links, since that converts my formulas (formula, in this simple example) to numbers

UPDATE I've encountered this issue before and didn't have a solution other than manually fixing the text. When I was replicating the problem to a colleague, the above find-and-replace method actually worked. I don't think this has ever worked for me before. Does anyone have any ideas????

Sort of not-a-terrible-Solution: Use a Formula

As I'm posting this, stackoverflow tried to see if my question was answered before and it got me thinking which got to a solution that isn't horrendous, though I was hoping to use the GUI find/replace feature: A1 (formula): =COUNTIFS(Data[ColAmt],"<=10") B1 (text): 'MyOldWorkbook.xlsx'!

1. Use this formula in C1 =SUBSTITUTE(FORMULATEXT(A1),B1,"")
2. Copy and paste-vales from C1 into D1
3. Done

Terrible (but relatively functional) solution: Paste As Text etc.

1. Format formula cell as TEXT (or paste formula in a cell formatted as text)
2. Find/Replace "=" with "" to remove Excel thinking it's a formula (or otherwise remove "=")
3. Find/Replace "'MyOldWorkbook.xlsx'!" with ""
4. stitch back formula in some way and copy text back to original formula cell

Answer 1

You can programmatically remove references to different workbooks in Excel formulas using either a macro or VBA script.

You can loop through all of the cells in a worksheet and modify the formulas to remove any references to external workbooks. I have created an example below:

Sub remove_external_references()
    Dim cell As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet 'Change to the desired worksheet

    For Each cell In ws.UsedRange
        If cell.HasFormula Then
            cell.Formula = Replace(cell.Formula, "[", "")
            cell.Formula = Replace(cell.Formula, "]", "")
        End If
    Next cell
End Sub

This script uses the Replace function to remove any square bracket characters "[" and "]" from the formula, which are typically used to indicate a reference to an external workbook.

You could of course change it anyway you want to, for example: cell.Formula = Replace(cell.Formula, "'MyOldWorkbook.xlsx'!", "")

Additionally, you can use the Workbook.LinkSources property to get the list of external links for a workbook, and use the Workbook.ChangeLink method to update the references.