How can I search and replace parts of functions?

[excelos]

Hello,
I want to search and replace the following:

=MAX(some-function,0)
with
=some-function

I tried to search for MAX( in order to delete it (and then I would delete the ,0) part but it does not work!
Any idea?
Thanks!

 

[Special-K99]

What value is the "some-function" ?
Are you sure you've spelt it correctly?

Maybe try searching for ",0)" instead?

 

[XOR LX]

Hi,

Do a Find and Replace to change all "=" to e.g. "##" first, thus rendering your formulas as text, then carry out the two operations you give, and then finally replace all "##" back to "=", forcing Excel to once again consider them as formulas.

Note that any array formulas will need to be recommitted as such after carrying out this process.

Regards

 

[Special-K99]

Do a Find and Replace to change all "=" to e.g. "##" first...

That's good thinking!

 

[XOR LX]

That's good thinking!

Thanks!

 

[Rick Rothstein]

Assuming some-function is named and specified correctly, just select the cell or cells with the formula you want to modify, then call up Excel's Replace dialog box (CTRL+H), put everything after the equal sign in the "Find what" field, put what you want after the equal sign in the "Replace with" field, then click the "Options>>" button and make sure the "Look in" dropdown says "Formulas" and the "Match entire cell contents" checkbox is not checked, then click "Replace All".

 

[XOR LX]

Assuming some-function is named and specified correctly, just select the cell or cells with the formula you want to modify, then call up Excel's Replace dialog box (CTRL+H), put everything after the equal sign in the "Find what" field, put what you want after the equal sign in the "Replace with" field, then click the "Options>>" button and make sure the "Look in" dropdown says "Formulas" and the "Match entire cell contents" checkbox is not checked, then click "Replace All".

I presume that some-function is not identical in all cells, but perhaps row- or column-relative, for example.

Regards

 

[Tom.Jones]

I presume that some-function is not identical in all cells, but perhaps row- or column-relative, for example.

Regards

Hello,

As far as I understand, it's about replacing the MAX function in the formula and remaining just "some-function".

In this case it does not matter "some-function", because that part, will remain in the formula and the MAX function, can be replaced with the method proposed by Rick.
I often use this method and replacing (Ctrl-H) is done without errors.

 

[Rick Rothstein]

As far as I understand, it's about replacing the MAX function in the formula and remaining just "some-function".

In this case it does not matter "some-function", because that part, will remain in the formula and the MAX function, can be replaced with the method proposed by Rick.

I believe the point XOR LX was making is the "some-function" could have relative cell reference arguments and that if it did, Replace would not work or a range because the cell address on each line would be different from each other. What I suggested would work only for single cells or multiple cell ranges where "some-function" is a function that takes no arguments.