Geek asks and answers

How to convert the actual excel formula (and not the result) to a text string

I was looking for a solution to a solution, and I could not find it.

Say I'm typing cell A1: = if (A2> 1,1,0)

Excel interprets this and returns 1 or 0.

However, what I would like to do is literally convert this formula to a string: '= if (A2> 1,1,0)'

If you press Ctrl + ~ in Excel, you will see all the formulas. In principle, what I want to do, in conclusion, I take all these formulas and make them lines.

(The goal, if you're interested, is to compare these lines with another book to make sure that I did not delete anything)

+5
source share
5 answers

To do this, you can create a user-defined function.

Module ( , )

Function GetFormula(rng As range, Optional asR1C1 As Boolean = False) As String
    If asR1C1 Then
        getFormula = rng.FormulaR1C1
    Else
        getFormula = rng.Formula
    End If
End Function


=GetFormula(A1)

, R1C1

=GetFormula(A1,TRUE)
+11

FORMULATEXT. Excel.

+7

, , string: '= if (A2 > 1,1,0)'

, :

Range("A1").Select
Dim strFormula As String
strFormula  = ActiveCell.Formula
MsgBox (strFormula)
+2

, find replace. .

+1

Just right-click on the column heading (for example, A) and select Format Cells, and then on the Number tab, select Text. Then, in each cell, press enter, and instead of the result, the formula will be displayed.

-1
source

More articles:


All Articles