Trying to Clear the same part of a formula in multiple cells...w/ No luck

Hi,



I have about 180 cells, all of which have


I want to delete the old file path from every cell but still retain the ending, which is a link to data from another sheet and a formula with being the other sheet name and "!$F$8" being the formula. Every time I try to do "find and replace" by copying the old file path and searching for it in the selected cells, replacing it with nothing gets rid of the the ' ' around the sheet I'm referring to, consuquently getting rid of my path to that sheet. Any help is appreciated. Thanks

I have often done this kind of thing in a few steps.

1st SAVE your file in case you mess up!!!!

1. convert it to text - F/R = with &= (or something like that)
2. F/R to make your change
3. F/R &= with =

Select the data and do a find and replace on "\\wr-Ts1\Jobs\Jack\[6-7-16 Estimating Template reconstruction.xlsx]" to nothing.

EDIT:
Are you sure that doesn't work for you?

Are you sure you didnt replace "'\\wr-Ts1\Jobs\Jack\[6-7-16 Estimating Template reconstruction.xlsx]" with nothing?

Originally Posted by JF1998

Can you explain what you mean by F/R? Im pretty new at this and im learning as I go here.

Find/Replace

Yeah I tried that and what happens is, clearing that also clears the ' ' around the sheet name, which gets rid of my link to a sheet in my current book. so for example, if I have " and try to get rid of , it also erases the apsotophes that should then be surrounding . Basically, I want in the end.

Can you attach a sample?

Originally Posted by JF1998

Can you explain what you mean by F/R? Im pretty new at this and im learning as I go here.

I'm not as new and I didn't know what F/R was either.

Apologies for the short-hand, figured if you typed out Find and Replace, you would make the connection to F/R

Desensitize the data.
Change the names and numbers but make the general idea something that will help us help you.

This is the point of making a representative sample.

Here is the line of formula I have.


Capture.JPG

It is supposed to reference a cell from another worksheet in the workbook. That formula however was copied from an entirely different workbook, so it has that old file link. Im trying to get rid of the file link but retain the string of numbers after it (R1876353)