Find and Replace in a relative FORMULA

[Excel Chimp]

Suppose this is your formula:
=+John!C34+Gary!C34+Tim!C34+Cindy!C34+Peter!C34

How would you eliminate the "Cindy!c34" part across many cells? I thought of using Find and Replace (ctrl + h), but the cell reference changes in these cells so that
I wanted to use some text formulas such as FIND and SUBSTITUTE to try to eliminate them, but don't know how to do this for a value within a formula.

Any input is very appreciated!

 

[Ron Coderre]

Perhaps using this replacement criteria:
Find_What: +Cindy!???
Replace with: (leave this blank)

Is that something you can work with?

 

[Excel Chimp]

That's great Ron. Good to know about the "?" special character.

I do wonder about a followup question: How would you use formulas in the case given? for example, if you wanted to use SUBSTITUTE or similar function on a formula?
How does one copy and paste the formulas themselves en masse to another sheet for manipulation? I know CTRL + ` switches to formula view, but seems one cannot copy and paste the formulas to another sheet.

Thanks again!

 

[Ron Coderre]

Try this:
To prepare the formulas for copying:
• Select the range of cells to be copied
• CTRL+H
...Find what: =
...Replace with: |=
...Click: Replace All

• Select the upper left destination cell to receive the copies

Restore the formulas
• CTRL+H
...Find what: |=
...Replace with: =
...Click: Replace All

Is that something you can work with?

 

[Excel Chimp]

Yes, that could work. So essentially, convert them from a formula to a non-formula in order to manipulate. Makes sense.
Thanks for the input!