Any function which return the formula of a cell?

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Thank you.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

earlyd

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
Hi,

You could try this simple UDF:

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

HTH

Alan
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

gaftalik said:
... 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

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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! :LOL:

Thanks everybody .
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
HI Phil

You don't really need the Offset():

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,872
Messages
5,766,864
Members
425,382
Latest member
IronM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top