The basic goal is to remove part of a formula so that the new formula is looking at the correct cell. For example, I want to get the formula

='C:\filepath\[filename]Sheet1'!A4

To be

='Sheet1'!A4

So I am basically trying to remove the file name from the cell. So that it will look at the sheet in the current file rather than the previous one. The issue is that the file path or name won't always be the same. I have tried to use the I have tried to use Filter and Replace mixed with wildcards, but no luck.