Results 1 to 5 of 5

Thread: How to extract input values used in a formula?

  1. #1
    Neophyte guru's Avatar
    Join Date
    Sep 2019
    Posts
    3
    Articles
    0
    Excel Version
    2010

    How to extract input values used in a formula?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    A input file has a column uses formula with different values.
    Example: A cell uses a formula X * Y, say =3*4 and returns 12.
    Now I want to extract the input values, i.e 3 and 4, from that cell and display X and Y values in two columns.
    Please help.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,691
    Articles
    0
    Excel Version
    365
    If the formula is in cell B2 then for the first number:
    =VALUE(LEFT(MID(FORMULATEXT(B2),2,50),FIND("*",MID(FORMULATEXT(B2),2,50))-1))
    for the second number:
    =VALUE(MID(MID(FORMULATEXT(B2),2,50),FIND("*",MID(FORMULATEXT(B2),2,50))+1,20))

    Click image for larger version. 

Name:	2019-09-30_181418.jpg 
Views:	14 
Size:	11.6 KB 
ID:	9387
    Last edited by p45cal; 2019-09-30 at 06:20 PM.

  3. #3
    Neophyte guru's Avatar
    Join Date
    Sep 2019
    Posts
    3
    Articles
    0
    Excel Version
    2010
    Good. Thank you. But "FORMULATEXT" fucntion is not available in Excel 2007. Is it only available in higher version or any additional module is required?

  4. #4

  5. #5
    Neophyte guru's Avatar
    Join Date
    Sep 2019
    Posts
    3
    Articles
    0
    Excel Version
    2010
    Thank you.

Tags for this Thread

Posting Permissions

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