+ Reply to Thread
Results 1 to 16 of 16

FORMULATEXT Function Equivalent for 2007

  1. #1
    Registered User
    Join Date
    06-14-2016
    Location
    Brookland, Arkansas
    MS-Off Ver
    2007
    Posts
    89

    FORMULATEXT Function Equivalent for 2007

    Like the title says, I am wondering if there is an equivalent function to FORMULATEXT in ME 2007. I haven't been able to find one.
    Who needs a life when you have Excel.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,492

    Re: FORMULATEXT Function Equivalent for 2007

    If I am understanding, you wish to have the formula appear as text in the cell. To do this, click on Formulas Tab and then on the Show formulas. If this is not what you are looking for, then please explain further as we are Excel experts but not mind readers.
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-14-2016
    Location
    Brookland, Arkansas
    MS-Off Ver
    2007
    Posts
    89

    Re: FORMULATEXT Function Equivalent for 2007

    What the formula text function does is actually treats the referenced cell formula as a text string. What this does is allow you to use such functions as substitute and replace (or any text function) on those before mentioned formulas, and make changes to them without having to manually go in. This function is also useful where you have several helper cells that you want to string together for compactness in the sheet (with the help of concatenate or &). It is a great function to use, but it is not available in the version of excel I have, and I was asking what equivalent function/process is available (for my version of excel) that converts referenced cell formulas into text strings. Aside from copy&paste.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FORMULATEXT Function Equivalent for 2007

    There is no built-in function equivalent in Excel versions 2010 and earlier.

    There is an old XL4 macro function that will do it but you may as well go all the way with a VBA user defined function.

    Are you familiar with using macros?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    06-14-2016
    Location
    Brookland, Arkansas
    MS-Off Ver
    2007
    Posts
    89

    Re: FORMULATEXT Function Equivalent for 2007

    I am familiar with macros, but I am definitely limited to the basics in that realm of excel. I know how to record/assign them, and can stumble my way through basic manual coding if the recorded code is not too complicated. But beyond that, no.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,492

    Re: FORMULATEXT Function Equivalent for 2007

    @TheMechEngineer Thanks for enlightening me. Was not aware of this function as I have not previously needed it.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FORMULATEXT Function Equivalent for 2007

    Copy the code below and paste it into a general module:

    Please Login or Register  to view this content.
    Then, to use it as a worksheet function...

    =GetFormula(cell address)

    =GetFormula(A1)

    You'll have to save the file as a macro enabled file in the *.xlsm file format.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FORMULATEXT Function Equivalent for 2007

    Quote Originally Posted by alansidman View Post
    @TheMechEngineer Thanks for enlightening me. Was not aware of this function as I have not previously needed it.
    It was introduced in Excel 2013.

  9. #9
    Registered User
    Join Date
    06-14-2016
    Location
    Brookland, Arkansas
    MS-Off Ver
    2007
    Posts
    89

    Re: FORMULATEXT Function Equivalent for 2007

    Thanks Tony,

    Just tried it, and it worked.

    I have been wanting to get into the Visual Basic aspect of Excel for reasons just like this.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FORMULATEXT Function Equivalent for 2007

    You're welcome. Thanks for the feedback!

  11. #11
    Registered User
    Join Date
    08-14-2019
    Location
    London,UK
    MS-Off Ver
    10 (sad)
    Posts
    1

    Re: FORMULATEXT Function Equivalent for 2007

    Almost 3 years later and this fix just saved me. (Yes some people are still stuck in 2010)

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: FORMULATEXT Function Equivalent for 2007

    Ha! In Northern Ireland, we're still stuck in 1690...
    Glenn



  13. #13
    Registered User
    Join Date
    10-21-2014
    Location
    Georgia, United States
    MS-Off Ver
    2007
    Posts
    5

    Re: FORMULATEXT Function Equivalent for 2007 - ARRAY Formulae

    Nearly another year on and it is STILL getting attention. Thanks! In my case, I found this, created a UDF and then found I already had one. Apparently I'd needed to include getting the text of Array Formulae so here's the adaptation in case there are any other deadheads needing zombie threads for our old, but good, licensed programs!
    Please Login or Register  to view this content.
    Why "Public"? Because I store the functions in Personal.xlsm so that they are available to other workbooks as shown in the commented example.
    Hope that helps someone who got in the same bind that I was in.

  14. #14
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    451

    Re: FORMULATEXT Function Equivalent for 2007

    As suggested above XL 4.0 macros are sufficient if someone does not wish to go for a UDF:

    All you need to do is define a name of: =GET.CELL(6,Sheet1!G12)
    I was in H12 when I defined the name. It is important to remove any $ in the range reference thereby creating a relative reference.
    The name given here was TEXTFORMULA
    This way, typing =textformula will always give the text value of any cell's formula to its immediate left

  15. #15
    Registered User
    Join Date
    10-21-2014
    Location
    Georgia, United States
    MS-Off Ver
    2007
    Posts
    5

    Re: FORMULATEXT Function Equivalent for 2007

    Quote Originally Posted by RaulSerg View Post
    As suggested above XL 4.0 macros are sufficient if someone does not wish to go for a UDF:

    All you need to do is define a name of: =GET.CELL(6,Sheet1!G12)
    I was in H12 when I defined the name. It is important to remove any $ in the range reference thereby creating a relative reference.
    The name given here was TEXTFORMULA
    This way, typing =textformula will always give the text value of any cell's formula to its immediate left
    Yes I tried that solution when I first came across this problem and used other XL 4.0 macros trying to solve other problems. However they rendered workbooks simply unworkable including crashing the application. Hence the solution to use a very simple UDF. I am only replying to ensure that those less versed in Excel functions and/or VBA don't head down what is a risky path using XL 4.0 with all the compatibility problems.

  16. #16
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    451

    Re: FORMULATEXT Function Equivalent for 2007

    Yup, I've read about some crashing problems involving 4.0 macros.
    I myself cannot report any such case over the last 15 years though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ?Offset and formulatext functions?
    By atung in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-19-2016, 01:03 PM
  2. [SOLVED] Looking for an equivalent of RIGHT function in VBA
    By strud in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2013, 06:15 AM
  3. Window Menu - Excel 2007 Equivalent
    By ffffloyd in forum Excel General
    Replies: 3
    Last Post: 08-08-2011, 02:19 AM
  4. maxif equivalent function
    By kamelkid2 in forum Excel General
    Replies: 4
    Last Post: 06-20-2011, 09:47 AM
  5. vba equivalent of SQL's IN function?
    By larbadar in forum Excel General
    Replies: 5
    Last Post: 07-24-2010, 06:16 PM
  6. Replies: 3
    Last Post: 02-03-2010, 01:32 PM
  7. [SOLVED] VBA equivalent for SQL 'IN' function
    By Mitch in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2006, 07:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1