Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Code To Extract Formula From a Cell

This is a discussion on Code To Extract Formula From a Cell within the Excel Questions forums, part of the Question Forums category; Dear All, First, I want to wish this board & you all a very happy & prosperous new year. My ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    43

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

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

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,458

    Default

    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. #4
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default

    Try another way ()no VBA :

    Clt + ~ ( Keyboard above of Tab)

    still can get ='BABY NEEDS'!AB19

    Regards
    Bosco

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default

    Try another way no VBA :

    Ctrl + ~ ( Keyboard above of Tab)

    still can get ='BABY NEEDS'!AB19

    Regards
    Bosco

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,458

    Default

    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. #7
    New Member
    Join Date
    Aug 2002
    Posts
    43

    Default

    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. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,458

    Default

    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. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,333

    Default

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


    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Aug 2002
    Posts
    43

    Default

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com