Code To Extract Formula From a Cell

Thanks:  0
Likes:  0

# Thread: Code To Extract Formula From a Cell

1.
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?

Excello.

2. 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.

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

Code:
```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.

4. Try another way ()no VBA :

Clt + ~ ( Keyboard above of Tab)

still can get ='BABY NEEDS'!AB19

Regards
Bosco

5. Try another way no VBA :

Ctrl + ~ ( Keyboard above of Tab)

still can get ='BABY NEEDS'!AB19

Regards
Bosco

6. 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.

7. 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

8. 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))

9. 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,"!")

10.

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..

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•