How to mass edit complex formulas? Wildcards?

[Purple Dragon]

I have a bunch of formulas on a sheet of the form:
=whole_buncha_stuff, ROUND(SUM(whole_buncha_otherstuff),1),more_other_stuff)

I want to remove the ROUND() function:
=whole_buncha_stuff, SUM(whole_buncha_otherstuff),more_other_stuff)

where the "whole_buncha...." strings represent variable content.

Naturally, I tried simple find/replace with replacing ROUND(SUM( with simply SUM(, intending to do a similar thing with the ,1) at the end; but before I can do the second step, Excel gives me the "error in your formula" message and no replacements are made.

My next thought is to use wildcard replacement: *ROUND(SUM(*,1),* with *SUM(*),*
However, I cannot find ANYWHERE on this forum what the syntax for wildcards in the "with" field would be. Microsoft has that in Word; I assumed something similar in Excel, but no...

I did read one post where the user had stripped the starting "=" sign and then used CTRL-H (in the two-step like I outlined above), then put the "=" back. However, in his case, the stuff between his = and his target text was short and fixed. Mine is neither, and I'm not sure how I'd isolate the cells that need the = put back in...

Any thoughts on how to accomplish this?

 

[AliGW]

What I do is copy the formula into Word, I use find and replace there, then I copy the formula back into Excel. Then I copy it across and down to where it needs to be. Hope this helps!

 

[Purple Dragon]

Wouldn't you know it... as soon as I posted the above, I figured it out. Why couldn't I have done that 3 hours ago?

So, in case someone else is struggling with the same thing, here's what I did:

1) Replaced "=IF" with ABCD_IF (This turns off the formula checking).
2) Did my two-step as above: Replace ROUND(SUM( with simply SUM( and then ,1),"" with simply ,""
3) Finally, replace ABCD_IF with =IF to restore the formula.

In general, it seems the trick is:

1) Substitute the beginning "=" to make the Excel think is it not a formula. Replace it with some unique tag easily "found" in the last step.
2) Make your CTRL-H substitutions in as many steps as needed.
3) Replace your unique string from #1 with the "=" to make it back into a formula again.

My apologies if this thread breaks the rules. I don't have delete or edit privileges yet.

 

[AkaTrouble]

just to try for future reference

if you press CTRL + tilda key ( the key on number row next to the 1 key) it will show formulas and then CTRL + H find and replace

 

[Purple Dragon]

Thank you, Ali!

However, I have some 200 formulas spread out all over the sheet, all of similar format but widely different content. While some of the content difference is indeed cell indexing, and your suggestion of using the formula propagation across and down as needed would work, there were simply too many unique instances to make that a quick solution. Of course, had I not figured out the more automated way to do it, this would likely have been my next (only?) choice.

Cheers!

 

[AliGW]

Useful thread, though - I've learnt a couple of new things, too!