2

e.g. if cell A1 contains the function =SUM(B1:B6) is there a way I could use a formula like SUBSTITUTE(A1,"SUM","COUNT") to return the formula =COUNT(B1:B6)?

Alternatively, is there a formula that will return what has been entered - i.e. return =SUM(B1:B6) as a string instead of the value?

ajor
  • 173

2 Answers2

2

There is a FORMULATEXT which is available from version 2013.

So you can use both
FORMULATEXT(A1)
and
SUBSTITUTE(FORMULATEXT(A1),"SUM","COUNT")
Second will result the formula as a text, you can't evaluate it.

For versions before 2010 you can do it only with VBA.

Máté Juhász
  • 22,071
2

This is really a supplement to Máté Juhász's answer rather than a complete solution to the question. An answer is the only way to include an image and this approach may be limited as far as which versions of Excel it will work with.

I mentioned the EVALUATE function in comments on the question and on Máté's answer. I don't have access to Excel 2013 (and don't know to what extent Máté may have tested this). I did a quick test on Excel 2007, which, unfortunately, doesn't support the FORMULATEXT function. So I couldn't apply this directly to that answer. However, here is the nature of how it would be used; it would need to be adapted to the actual solution.

screenshot

Cell A1 contains the original formula in the question. A2 contains the substituted text (had to hard code the A1 formula since FORMULATEXT wasn't supported). A3 is a reference to a named range, which contains the EVALUATE function applied to the substituted formula.

If this works in Excel 2013, the solution would look like this:

Create a named range (call it, say, "formula"), defined as:

=EVALUATE(SUBSTITUTE(FORMULATEXT(A1),"SUM","COUNT"))

Refer to the named range for the result:

=formula
fixer1234
  • 28,064