Is there a way to fetch the text of a formula of another cell into an excel cell?

[TomCon]

What i want is the equivalent of the VBA formula property, on the Excel sheet.
For instance in VBA i can say the following
x=Range("A1").formula
And it puts the text of the formula in cell A1 into the variable x.

I'd like to put the text of the formula in cell A1 into cell B1, say. Not as a formula, but as text. And with a formula, not by copy/paste. (I want to fill the formula down).

Is there a way?

Thank you.

 

[Micron]

If you want to use a formula then I don't know how because I suspect that any formula you would use has to start with "=", thus negating the attempt to put a formula in a cell as text only. If you want to use vba then you could use range("B1")= "'" & .range(A1").formula

 

[TomCon]

First of all this could be solved with a UDF written in VBA.

But i really try hard to avoid UDFs as they are called way too often, unnecessarily, even when non-volatile. This can be seen by putting a breakpoint in a UDF and then entering a value in a random empty cell in a workbook unconnected to the UDF (no other cell in the workbook is dependent on this cell) and you will see the UDF is called...many times. It has greatly impacted (reduced) my use of UDFs.

I want to parse the formula of one cell and put a parsed result in another cell (without using a UDF!).

For example.
If A1 contains
=SUM(H1000:H1500)
I want the number "1000" to be the result.

So, in psuedo code it might look like this
=VALUE( MID(formula text in A1, FIND( "H", formula text in A1)+1, 4))

Thanks if any ideas!

 

[Micron]

Never have seen that behaviour and have done lots of debugging while trying to learn. I could envision what you're saying if the code was in personal workbook though. Sometimes though, a guy's gotta do what a guy's gotta do. At least now if you get a formula solution I'll know that referring to a range formula can be done in a formula.

Maybe the built in FormulaText function if you have the right Excel version?

 

[Micron]

So if you have it that function, this will find "4" from B4
=MID(FORMULATEXT(E4),FIND("B",FORMULATEXT(E4),1)+1,1)
E4 contains =DAY(EOMONTH(B4,0))

but it's not at all volatile. Not sure how you get 1000 out of H1000 one time, 100 if it's H100, 10 if it's H10 etc. to say nothing of changing column letters.

 

[kevin9999]

Is it something like this?

Book1
	A	B	C
1	16/09/2023	=TODAY()
2	Hello 2	="Hello "& ROW()
3	1 - 3	=COLUMN()&" - "&ROW()
4	156	=SUM(H1:H1000)	1000
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
B1:B4	B1	=FORMULATEXT(A1)
A2	A2	="Hello "& ROW()
A3	A3	=COLUMN()&" - "&ROW()
A4	A4	=SUM(H1:H1000)
C4	C4	=TEXTBEFORE(TEXTAFTER(B4,"H",2),")")

 

[TomCon]

Wow thanks, that is what i was looking for, the Excel built-in function FORMULATEXT. I googled various things but it did not turn up. Guess i did not describe it exactly enough for that to turn up as an answer. But, thanks much! Always want to avoid UDFs (as outlined above)!
Tom

 

[kevin9999]

Happy to help Tom, and thanks for the feedback

 

[Micron]

So if you have it that function, this will find "4" from B4

Well that wasn't good grammar was it? How about "So if you have it, that function will find "4" from B4"
Glad I could help. FWIW, I think my search term was "excel formula to get a cell formula" which led me to

FORMULATEXT function - Microsoft Support

Syntax: FORMULATEXT(reference)

support.microsoft.com

Baring my soul here - google is how I manage to answer some 75% of the questions I respond to.
Thanks for the recognition.