Surgically Replacing Parts of Formulas in Excel

Problem

Sometimes there are parts of an overall formula that need to be removed from a number of cells all at once but a simple find and replace will result in an error.
To try and find a fix I went on a fruitless google hunt with the following searches:

• replace only part of a cell in excel
• find and replace with wildcards in excel
• advanced find and replace excel
• find and replace the beginning and end of a formula

The best results involved VBA, but I knew there had to be something better.

It might seem easy enough to just use the Ctrl + H and replace what you need to with a single short cut. The tough part is when the formula has multiple criteria that you need to remove, it will produce an error while trying to calculate the new cell right after replacement.

After thinking about it for a while and breaking down the error, this is what I came up with as a work around.

Solution

The key step in allowing specific portions of a function to be removed in multiple cells is by turning the cells into text. The character that is telling the program to calculate each cell is the "=" at the start of the cell. The process I have to remove specific sections of a formula and restore the formula into working order consists of converting it to text, using find and replace to edit the text in multiple cells, and then restoring the text to an equation by inserting a "=" in the front again. Here's what I mean.
In the specific project I'm working on I want to remove the ROUND function from a large table of cells.

1. Select the cells with the formula you would like to edit, go to the ribbon, click on Find & Select,  and click on Replace.
   
2. In the Find what field enter: =
   In the Replace what field enter a unique string such as: SPOCK
   Press the "Replace All"
3. With the cells you need edited still selected, in the Find What field enter the first part of the formula you would like changed with what you would like it changed to in the Replace with field.
   In this case I will replace "+ROUND" with nothing to get rid of rounding.
   
4. Repeat step 3 with any other parts of the equation you needed changed. I will remove the end of the Round function ",0)" with ")" to remove all rounding from these cells. This step can be done as many times as you need to edit the formula, but it will be key to leave the unique string you entered in in step 2 for the final step.
5. For the final step to restore it back to a functioning equation, use the Find and Replace tool to replace your unique string with an "=". In my example I will replace SPOCK with "=."
   

If all worked you will have a group of newly designed equations that have been adjusted to exactly what you need.  

Have you ever run into this