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

Convert text to Formula in Excel 2007

This is a discussion on Convert text to Formula in Excel 2007 within the Excel Questions forums, part of the Question Forums category; Hello Forum, I need to build interactive formulas as part of a bigger table. I have come only this far ...

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    2

    Default Convert text to Formula in Excel 2007

    Hello Forum,

    I need to build interactive formulas as part of a bigger table.

    I have come only this far with the test model:

    A1 1 (or any value)
    B1 + (or any operator)
    C1 2 (or any value)
    D1 =1+2 as the result of ="="&A1&B1&C1 in text format

    Now I want E1 to show the result. In this case a total of 3

    How can I convert D1 to a formula in E1 that calculates the result and changes with any of the input values? I have spend ours on it without results . Can something this simple be that complex; PLEASE

    Sebastiaan

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,926

    Default Re: Convert text to Formula in Excel 2007

    Welcome to the Board!

    Maybe try in E1:

    =eval(d1)

    or:

    =eval(A1&B1&C1)

    Hope that helps.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

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

    Default Re: Convert text to Formula in Excel 2007

    Quote Originally Posted by schielrn View Post
    Welcome to the Board!

    Maybe try in E1:

    =eval(d1)

    or:

    =eval(A1&B1&C1)

    Hope that helps.
    That requires the MoreFunc Add-In.
    Microsoft MVP - Excel

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,835

    Default Re: Convert text to Formula in Excel 2007

    Pretty sure you need VBA to do that.
    Here's a basic UDF to do it. May need tweaking for building complex formulas in this manner. But works for the basic example you provided.

    Put this code in any standard module
    Code:
    Public Function Eval(myval As String)
    'Evaluates a text string as A formula
    Eval = Evaluate(myval)
    End Function
    Then you can use this in a cell formula

    =EVAL(A1&B1&C1)
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,926

    Default Re: Convert text to Formula in Excel 2007

    Ahhh. Thanks. I didn't think it was a native formula, but knew there was evaluate in vba, so figured it might be. But thanks for spotting that out.

    So if you want to use my formula you will need to download the morefunc add-in.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  6. #6
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,926

    Default Re: Convert text to Formula in Excel 2007

    Depending on how complex your operands can be, you could use an if statement:

    =IF(B1="+",A1+C1,IF(B1="-",A1-C1,IF(B1="/",A1/C1,A1*C1)))
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,116

    Default Re: Convert text to Formula in Excel 2007

    EVALUATE is available to Names.

    (After selecting E1 per: proper relative addressing )
    one could define a name Name: formulaResult RefersTo: =EVALUATE(Sheet1!$D1)

    Putting =formulaResult in a cell (in row1) will then return the desired result.
    Last edited by mikerickson; Feb 9th, 2010 at 10:05 AM.

  8. #8
    New Member
    Join Date
    Feb 2010
    Posts
    2

    Default Re: Convert text to Formula in Excel 2007

    The =Eval or Evaluate function looked very promising and have tried the functions standalone and in a named cell. Sad to say, neither works. They do not appear to be standard dunctions. I'll see if I can download the function somewhere.

    I will try to find and download "MoreFunc Add-In" as suggested. Hope that goes smoothly. Back to GGLE..............

    This is where I went:

    http://www.freedownloadscenter.com/B..._Download.html

    Download creates a setup file. Closed Excel, started setup, followed the instructions and SUPER. Re-started Excel and I have a whole series of new functions on the Formula ribbon. EVAL was there and worked. Problem solved.

    Thank you all!

    Sebastiaan

    Sebastiaan

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,116

    Default Re: Convert text to Formula in Excel 2007

    EVALUATE will not work when in a formula that is entered in a cell.
    EVALUATE will work when part of the definition of a Name.

    e.g. Define a name
    Name: Test
    RefersTo = EVALUATE("2")

    then,
    putting =EVALUATE("2") in a cell will return an error
    putting =Test in a cell will return 2.

  10. #10
    New Member
    Join Date
    Oct 2010
    Posts
    1

    Default Re: Convert text to Formula in Excel 2007

    Hi evrybody,
    Does enybody knows similiar formula that works with reference on closed workbook?

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