Excel 2016 Inserting an ' at the Front of Formulae After Find and Replace

[andybason]

Sometimes I need to move cells containing formulae to different books/sheets. To do this without affecting the formula references I find/replace the = with a #. Once moved I find/replace the # with = to reinstate the formulae.

For some reason Excel inserts a ' at the start of each formula after the second find/replace. It does not do it with all formulae, only some. I am using Excel 2016.

Eg. '=COUNTIFS(PostTable[Movement %],">=" &$A130,PostTable[LTP],"<15")

To reinstate the formulae I have to manually delete the ' from each cell. Find and replace does not recognise the ' ("Excel cannot find a match") and the cells are not formatted as text, they are formatted as numbers.

I have 100s of formulae that are affected and it is painful to do it manually. Is there a quicker way to do this? Or, even better, how do I avoid the issue in the first place?

Any help would be greatly appreciated.

Thanks

 

[Sufiyan97]

Select the entire column with formula which contains " ' " at beginning and do a Text to Columns > Delemited > clear (untick) all delemiters and Finish.

 

[andybason]

This is brilliant Sufiyan. Thank you so much!

 

[Sufiyan97]

You're welcome.
Glad I could help!