Code To Extract Formula From a Cell

[Excello]

Dear All,

First, I want to wish this board & you all a very happy & prosperous new year.

My issue, is that I have a cell "B5" contain a link to another WorkSheet in the same WorkBook, the Cell contains (='BABY NEEDS'!AB19), while the WorkSheet "BABY NEEDS" Cell AB19 house a value of $ 550.

I just want a simple code to tell me the context of Cell "B5" (and not the value of it), or in other words, I want to now what is the function written in Cell "B5" as a text so I can extract the linked Sheet Name by using any of the text functions.

Can you help me on this?

Thanks in advance.

Excello.

 

[Juan Pablo González]

In the MoreFunc.xll AddIn from Laurent Longre, there is a function called FORMULATEXT that provides you with what you need. If not, you have to use VBA for this, I don't know of any builtin method in Excel to accomplish this.

 

[Andrew Poulsom]

Here is a custom function that will return a cell's formula:

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

Paste the code into a General module. Then enter this in a blank cell on your worksheet:

=CellFormula(B5)

and you will get ='BABY NEEDS'!AB19.

 

[bosco_yip]

Try another way ()no VBA :

Clt + ~ ( Keyboard above of Tab)

still can get ='BABY NEEDS'!AB19

Regards
Bosco

 

[bosco_yip]

Try another way no VBA :

Ctrl + ~ ( Keyboard above of Tab)

still can get ='BABY NEEDS'!AB19

Regards
Bosco

 

[Andrew Poulsom]

On 2003-01-02 10:22, bosco_yip wrote:
Try another way no VBA :

Ctrl + ~ ( Keyboard above of Tab)

still can get ='BABY NEEDS'!AB19

Regards
Bosco

That only displays the formula instead of its result. You cannot use text functions with it.

 

[Excello]

Juan, Andrew

Thank you all.
I got the written function in the Cell, but I put myself in another problem where I failed to extract & separate the Department Name "BABY NEEDS" out of the string & have the Cell reference "AB19" alone.

Also, I have discovered that some linked cells has the "'" at the begining and some dosen't have (I don't know why!!) as follows:

Case (1):
"'BABY NEEDS'!AB19"

Case (2):
"H.B.A.!AB19" is missing the "'" at the begining but still I got its value.

Case (3):
"'COSMETICS & PERFUMES'!AB19" a longer string where I want to get the sheet name "COSMETICS & PERFUME" alone & the Cell Reference "AB19" alone, and the same for other a/m strings.

I am lost in all text functions. Can you help me on this.

Thanks.... Excello

 

[Andrew Poulsom]

If the formula text is in cell C5, to get the sheet name use:

=SUBSTITUTE(MID(C5,2,FIND("!",C5,1)-2),"'","")

and to get the cell reference use:

=RIGHT(C5,LEN(C5)-FIND("!",C5,1))

 

[Aladin Akyurek]

On 2003-01-02 11:12, Excello wrote:
Juan, Andrew

Thank you all.
I got the written function in the Cell, but I put myself in another problem where I failed to extract & separate the Department Name "BABY NEEDS" out of the string & have the Cell reference "AB19" alone.

Also, I have discovered that some linked cells has the "'" at the begining and some dosen't have (I don't know why!!) as follows:

Case (1):
"'BABY NEEDS'!AB19"

Case (2):
"H.B.A.!AB19" is missing the "'" at the begining but still I got its value.

Case (3):
"'COSMETICS & PERFUMES'!AB19" a longer string where I want to get the sheet name "COSMETICS & PERFUME" alone & the Cell Reference "AB19" alone, and the same for other a/m strings.

I am lost in all text functions. Can you help me on this.

Thanks.... Excello

If you are using FORMULATEXT...

=SUBSTITUTE(WMID(FORMULATEXT(A1),1,1,"!"),"=","")

=WMID(FORMULATEXT(A1),2,1,"!")

 

[Excello]

Thanks to Andrew/Aladin,

I managed to get what I want if I inserted (edited) these functions directly to a cell, but I failed to get the same thru VBA code. Would you please help me on how to right a code to get me the department alone & cell reference alone ..

Many Many Thanks to all of you.
Excello..