+ Reply to Thread
Results 1 to 5 of 5

Is there anyway to do the reverse of =FORMULATEXT?

  1. #1
    Registered User
    Join Date
    07-10-2021
    Location
    GENEVA
    MS-Off Ver
    EXCEL FOR MAC MICROSOFT 365
    Posts
    80

    Is there anyway to do the reverse of =FORMULATEXT?

    Suppose I have a formula written as text, such as "=15+4", is there any way to convert this text back into a formula so that I will see the answer "19"?

    Simply put, I am looking for a function which does the same as =FORMULATEXT(), but in reverse.

    I Googled it and some respondent had written on another forum that it can't be done. I saw someone else had been able to use a Power Query called "Evaluate", but as I am running Excel on a Mac that kind of Power Query does not seem to be available to me.

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Is there anyway to do the reverse of =FORMULATEXT?

    In excel 2016 , we can use Evaluate as function in name manager.

    For sample.

    Select cell B1 then goto name manager,
    press New button,
    type the name you desire for sample CvtNum,
    in Refers to type
    =Evaluate(A1)
    press OK
    press close

    now you will got a function named CvtNum
    it will try to evaluate the left cell next to it.
    (You create that name while select B1 and refered to A1,
    A1 is the left cell next to B1)

    when you have any formula text , for sample in E5
    you can put =CvtNum in F5 to evaluate the text of formula in E5

    Regards.

    Note : I don't know did it works on mac.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Is there anyway to do the reverse of =FORMULATEXT?

    Try this UDF . It works.
    UDF code
    Please Login or Register  to view this content.
    How to Use UDF code:
    In the developer tab click--> Visual Basic
    VB window opens
    Insert--> Module
    Paste the code.
    Close the VB window.
    Now UDF is available in Function List
    Save file as .xlsm
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 03-08-2022 at 01:31 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    07-10-2021
    Location
    GENEVA
    MS-Off Ver
    EXCEL FOR MAC MICROSOFT 365
    Posts
    80

    Re: Is there anyway to do the reverse of =FORMULATEXT?

    Menem, Your solution works brilliantly on my Mac. I am glad there was no Macro or VBA necessary!
    Last edited by AliGW; 03-08-2022 at 04:51 AM. Reason: PLEASE don't quote unnecessarily!

  5. #5
    Registered User
    Join Date
    07-10-2021
    Location
    GENEVA
    MS-Off Ver
    EXCEL FOR MAC MICROSOFT 365
    Posts
    80

    Re: Is there anyway to do the reverse of =FORMULATEXT?

    kvsrinvasamurthy, Thanks very much for your very clear explanation which works - but only after I looked at your example. It is my first initiation into User Defioned Functions - I really learned something. Thanks so much. To start with I thought I had to type =Evaluate or =UDF, but all solved not. Thanks again.
    Last edited by AliGW; 03-08-2022 at 04:51 AM. Reason: PLEASE don't quote unnecessarily!

+ 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. Replies: 1
    Last Post: 03-08-2022, 05:01 AM
  2. [SOLVED] FORMULATEXT Function Equivalent for 2007
    By TheMechEngineer in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-23-2020, 05:20 PM
  3. Using worksheet FORMULATEXT function in VBA
    By RobinClay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2019, 02:32 PM
  4. [SOLVED] VBA problem - reverse an alphanumeric string, but reverse only letters and leave digits wh
    By mati_WAR in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-18-2019, 10:39 PM
  5. Replies: 7
    Last Post: 11-27-2018, 08:02 PM
  6. [SOLVED] Circular Reference with HYPERLINK and FORMULATEXT: why?
    By Requin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2017, 11:07 AM
  7. ?Offset and formulatext functions?
    By atung in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-19-2016, 01:03 PM

Tags for this Thread

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