Display actual typed formula in a cell

[winvj]

Hi guys we need your help for my below query.

I typed a calculation in cell A1 like =45+89+15 and the value shows 149 in same cell

I need to display the Calculated items which is "=45+89+15" in another cell B1.

How to do this either function or VBA

Advance thanks for your help.

 

[RoryA]

There is no built-in function in 2007 for that (2016 and later have FORMULATEXT) so you'd need a UDF written in VBA - for example:

Function GetFormulaText(Input as range) as string
getformulatext = input.cells(1).Formula
end function

then just use

=GetFormulaText(A1)

in B1

 

[winvj]

There is no built-in function in 2007 for that (2016 and later have FORMULATEXT) so you'd need a UDF written in VBA - for example:

[ode]Function GetFormulaText(Input as range) as string
getformulatext = input.cells(1).Formula
end function[/code]

then just use

=GetFormulaText(A1)

in B1

Hi Rory

thanks for your reply.

can you please explain. i am a beginner of excel, also mentioned the right formula which i need to cut & paste.

the line getformulatext = input.cells(1).Formula is showing error.

thank you.

 

[RoryA]

You need to open the VB Editor (Alt+f11) then with your workbook active, choose Insert - Module from the menu. Then paste in the code I posted:

Function GetFormulaText(Input as range) as string
getformulatext = input.cells(1).Formula
end function

You can then switch back to Excel and use the formula I suggested.

 

[Dave Patton]

another alternative follows

If you require a more complex solution, please advise.

T202111a.xlsm
	A	B	C
1	149	=45+89+15	=45+89+15
5d

Cell Formulas
Range	Formula
A1	A1	=45+89+15
B1	B1	=gf(A1)
C1	C1	=GetFormula(A1)

Function GF(Rng As Range) As String
  GF = Rng.Formula
End Function


Function GetFormula(Rng As Range) As String
 GetFormula = Rng.Formula
End Function

 

[winvj]

another alternative follows

If you require a more complex solution, please advise.

T202111a.xlsm
	A	B	C
1	149	=45+89+15	=45+89+15
5d

Cell Formulas
Range	Formula
A1	A1	=45+89+15
B1	B1	=gf(A1)
C1	C1	=GetFormula(A1)

Function GF(Rng As Range) As String
  GF = Rng.Formula
End Function


Function GetFormula(Rng As Range) As String
 GetFormula = Rng.Formula
End Function

thank you so much. it is working fine....

 

[Dave Patton]

Thanks for the feedback.