9 thoughts on “The Excel FORMULATEXT function”

1. Did you notice that FORMULATEXT doesn’t generate any circular reference errors? Put this in cell A1:

   =FORMULATEXT(A1)

   Or this

   =MID(FORMULATEXT(A1),18,2)

2. Smart, I guess. Apparently, Excel recognizes the formula doesn’t actually mess with its calculation engine and thus refrains from a circular reference error.

   Some time back (a long time back?) I noticed the same about OFFSET. In A1 enter =OFFSET(A1,0,1,1,1). No problem whatsoever. Same with =INDEX(C:C,1) in any cell in column C except C1.

3. So no longer need to use the old GET.CELL(6, range reference) via a named range – once I upgrade from Excel 2003…

4. What is the use case for this?

5. @Alex: Good question. Particulary since you can just push Ctrl + Grave Accent key (`) to toggle between Show Formulas and Show Results. Although I do occasionally use the method Pete suggests purely so I can demonstrate what it looks like to others for learning purposes.

6. Why would =FORMULATEXT(A1) entered in A1 behave any differently than =ROW(A1) entered in A1?

   In these cases ROW is returning the cell’s .Row property, and FORMULATEXT is returning its .Formula property. It’s only happenstance that in the FORMULATEXT formula the cell’s .Value and .Formula properties are the same, but it matters to the recalculation engine that FORMULATEXT returns the .Formula property.

   So slowly incorporating Longre’s MOREFUNC UDFs from years ago into Excel. Boldly providing a feature Lotus 1-2-3 added 23 years ago. Better late than never.

   As for use, pity it lacks a 2nd argument like INDIRECT’s with which users could opt for R1C1 addressing. Using R1C1 addressing it could be used to check whether different cells had the same formula in R1C1 addressing, that is, were copy-paste/[Ctrl]+[Enter] equivalent.

7. FORMULATEXT(long time coming!) is nice, but when pointed at a cell containing a number it returns #N/A . My books contain GETFORMULA, a slight improvement. Note the “formula.local” which returns the local language version of the formula (not sure what FORMULATEXT does … )

   ‘8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes
   Function getformula(r As Range) As String
   Application.Volatile
   If r.HasArray Then
   getformula = “<– " & " {" & r.FormulaLocal & "}"
   Else
   getformula = "<– " & " " & r.FormulaLocal
   End If
   End Function

8. I have just been experimenting with this feature and I will get a lot of use out of it in teaching students to create formulas in excel. I can now test to see if they have created the formula that I required and automatically comment on it from an adjacent cell.

9. Is it possible to do the opposite?

   For example, I’m going to have a list of references which will have formulas. (i.e. “(A+B)*E” or “((A+C)*B)/E” and user response will lookup the formula, and using named ranges I want it to calculate the answer based on the saved formula using named ranges.

   Hope that makes sense.
   Thanks!

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.