Extract part of an Excel formula in another cell

Question

I want to extract the numerical part of this formula, so I can work with it in another cell:

=8464184.94*$S35

The formula is in R35.

I did see the post in Is it possible to extract part of a formula in excel? but despite what is said there, there is no such function as EVALUATE() in standard Excel (in my Case Excel 2021 DESKTOP).

=RIGHT(LEFT(FORMULATEXT(R35),(FIND("*",FORMULATEXT(R35),1)-1)),LEN(LEFT(FORMULATEXT(R35),(FIND("*",FORMULATEXT(R35),1)-1)))-1)

works but is, as you can see, pretty ghastly.

Try: =TEXTAFTER(TEXTBEFORE(FORMULATEXT(R35),"*"),"=") don't think this needs the Evaluate() feature of Excel which uses Excel Macro 4.0 functions — Mayukh Bhattacharya, Commented Aug 5 at 13:03
Or with older versions: =LET(a, FORMULATEXT(R35),TRIM(LEFT(SUBSTITUTE(RIGHT(a,LEN(a)-1),"*",REPT(" ",100)),100))) — Mayukh Bhattacharya, Commented Aug 5 at 13:06
The first one returned a NAME error; second one worked. Not allowed to upvote your comment or answer, so thank you. I have to avoid macro functions, VBA and add-ins, otherwise these workbooks won't work for the the people I send them to. — Steev43230, Commented Aug 5 at 14:01
Is it always a multiplication? then maybe the reverse operation will provide the desired result: =R35/$S35 — cybernetic.nomad, Commented Aug 5 at 20:37
@cybernetifc.nomad - Yes, it is "always" multiplication in this specific instance. To Mayukh Bhattacharya - TEXTSPLIT(), TEXTAFTER(), and TEXTBEFORE() are not in Exce; 2021 (desktop, standalone version). At least not unless there is some special addin that has to be activated. — Steev43230, Commented Aug 6 at 1:59

Mayukh Bhattacharya · Accepted Answer · 2024-08-05 13:41:45Z

3

You could try using the following in Excel 2021

=LET(a, FORMULATEXT(B2),TRIM(LEFT(SUBSTITUTE(RIGHT(a,LEN(a)-1),"*",REPT(" ",100)),100)))

In MS365

=INDEX(TEXTSPLIT(FORMULATEXT(B2),{"=","*"},,1),1)

Or,

=TEXTAFTER(TEXTBEFORE(FORMULATEXT(B2),"*"),"=")

One more alternative :

=LET(
     a,FORMULATEXT(B2),
     REPLACE(SUBSTITUTE(a, RIGHT(a, LEN(a)-FIND("*",a)+1),),1,1,))

edited Aug 5 at 13:41

answered Aug 5 at 13:10

2,8282 gold badges6 silver badges15 bronze badges

Add a comment |

Ron Rosenfeld · Accepted Answer · 2024-08-06 12:16:52Z

0

For older Windows versions of Excel, you can use:

=FILTERXML("<t><s>" & SUBSTITUTE(MID(FORMULATEXT(R35),2,255),"*","</s><s>") & "</s></t>","//s[1]")

answered Aug 6 at 12:16

Ron Rosenfeld

9,0513 gold badges15 silver badges17 bronze badges

Add a comment |

2 Answers 2