Copy part of formula in other cell

[Arie Bos]

Formula in Y121: =((SUM(V116:W121)+SUM(S116:U121))/-SUM(S116:U121))

I want to copy the first part of the formula in the cell below, so "=((SUM(V116:W121)". When using the mid() function, it still returns the result value of Y121, but not the formula part. So, I thought the put the mid() function within a TEXT() function, but then the format_text argument is not clear to me for such operation.I think I am overlooking the simplest possible method, hence I post this here.
thanks in advance,
Arie

 

[Eric W]

If you have Excel 2013 or newer, try:

=MID(FORMULATEXT(Y121),1,17)

FORMULATEXT was added in 2013. I'm not sure why you'd want to do this. You can't build another function from it. (Unless you use VBA or the old Excel 4.0 macro Evaluate, but that's a whole different issue.)

 

[Arie Bos]

Thanks Eric,
I have 2013, but always use 2010 as for MDI issue, which is horrible in 2013. Anyways, I have to use it in case I want to use your solution.
Arie

 

[Arie Bos]

Eric,
I used your formula, but the result is now a string "SUM(V116:W121)". how to turn that into a formula on it's own?

 

[Eric W]

You can't turn it into a formula without VBA. What I'd recommend is to go about it the other way. Pick another cell, say Z121, and put
=SUM(V116:W121)

Now change your Y121 formula to:
=(Z121+SUM(S116:U121))/-SUM(S116:U121)

Now you have both values visible, and Y121 adapts immediately when Z121 changes.

If for some reason you still need to dissect the formula in Y121, press Alt-F11 to open the VBA editor, Insert a Module, and paste this code:

Public Function SemiFunction()

    SemiFunction = Evaluate(Mid([y121].Formula, 4, 14))
    
End Function

Then go back to your worksheet and enter

=SemiFunction()

in some cell. You can use a combination of VBA functions, like Left, Right, Mid, etc. to extract the parts you need and build a formula. But you'd need a fair amount of experience to create/maintain such a UDF, and it's still not a great way to do things. Far better to create the formulas on the worksheet in a way that they work together and give you the results you need.