excel replace string in formula

Question

Using Excel 2010, I have a sheet filled with "almost" the same formula. I need to replace all instances from =COUNTIFS to =1-(COUNTIFS and then the last ) needs replaced with ))

Trying to use CTRL+H gives errors that the formula is missing a )

Anyother smart way to get this done?

It gave a hint. But even though - it still gives same error. Perhaps replacing the last ) with )) to begin with.. and then replacing the =countifs.. — osomanden
– osomanden, Commented Jul 14, 2016 at 8:54

Taosique · Accepted Answer · 2016-07-14 08:52:27Z

1

There's a way if you're using Excel 2013 or newer.

Use FORMULATEXT() function to modify your formulae, like this: ="1-("&FORMULATEXT(A1)&")".
Copy and paste the results of this as values.
Now you have correct formulae as text. To force their evaluation simply select them and replace (Ctrl+H) = for =. This way formulae are not changed but forced to re-evaluate.

answered Jul 14, 2016 at 8:52

Taosique

7,9143 gold badges33 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel replace string in formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related