Any function which return the formula of a cell?

[gaftalik]

Hi,

Is there any function which can return the formula of a cell ?

Thank you.

 

[earlyd]

What do you mean by the formula of a cell - do you mean the cell reference? If so there is the ADDRESS function. If you mean a function that can contain a cell value and give you the value of that cell, then you want the INDIRECT function.

But I really don't know what you mean.

 

[al_b_cnu]

Hi,

You could try this simple UDF:

Function xxx(ByVal xyz As Range) As Variant
xxx = xyz.Formula
End Function

HTH

Alan

 

[gaftalik]

Hi Alan,

Thanks for the code , it worked perfect but if i have this in a formula :
=1+3+5

How to return 1 or 3 or 5
is it left(xxx(text or other ?

Thank you very much .

 

[Aladin Akyurek]

... but if i have this in a formula :
=1+3+5

How to return 1 or 3 or 5
is it left(xxx(text or other ?...

Entered in B1 and copied across:

=WMID(SUBSTITUTE(FORMULATEXT($A1),"=","",1),COLUMN()-COLUMN($B$1)+1,1,"+")

where A1 houses =1+2+3.

The formula requires the morefunc add-in.

PS. Why do you need this manipulation?

 

[gaftalik]

Thank you Champ ! you amaze me as always .

needed that not to reintroduce data separately from a copied sheet.

No sea will carry my ships of thanks to you Aladdin!

Thanks everybody .

 

[pbornemeier]

Zombie Thread Resurrected!

Needed to dynamically display the formula of a cell. This post helped: http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html and resulted in:

'Define a named range (Name='AboveCellFormula') with this as the formula: =GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),-1,0))
'Put =AboveCellFormula into a cell to display the formula above it

 

[pgc01]

HI Phil

You don't really need the Offset():

=GET.CELL(6,INDIRECT("R[-1]C",FALSE))