how to calc 3*2*5+6 in a cell with out Equal

user_17623598 0
Viewed 109

A function that can compute an expression such as (2 * 5 + 6=16 or

10*3*0.75=22.5) without equating.

i did it with execl but it is not complate

enter image description here

=IFERROR(LEFT(G2170,FIND("*",SUBSTITUTE(UPPER(G2170),"C","*"))-1)*RIGHT(G2170,LEN(G2170)-FIND("*",SUBSTITUTE(UPPER(G2170),"C","*"))),IF(LEN(G2170)=10,LEFT(G2170,1)*MID(G2170,3,2)+MID(G2170,6,2)+MID(G2170,9,2),IF(LEN(G2170)=8,LEFT(G2170,1)*MID(G2170,3,3)+RIGHT(G2170,2),IF(LEN(G2170)=7,LEFT(G2170,1)*MID(G2170,3,2)+RIGHT(G2170,2),IF(LEN(G2170)=9,LEFT(G2170,2)*MID(G2170,4,1)*RIGHT(G2170,4),IF(LEN(G2170)=5,LEFT(G2170,1)*MID(G2170,3,1)*RIGHT(G2170,1)))))))
vba excel
1 Answers

You can write an easy user defined function to evaluate those strings:

Option Explicit

Public Function EvaluateString(ByVal Formula As String)
    EvaluateString = Application.ThisCell.Parent.Evaluate(Formula)
End Function

The code needs to be in a Module.

It can be used as formula like EvaluateString(A1) and A1 can be any formula that Excel is able to evaluate. If the formula includes other formulas like SUM that contain words they need to be the english formula version no matter which localization your Excel uses as Evaluate only handles english formulas.

And you probably need to make this function Application.Volatile if the formulas contain references to other cells. Avoid that if there are no references for sure. Volatile functions will have a high calculation impact on your sheets (the get calculated everytime anything gets calculated on the sheet).

user_32196130
Related