CODEWITHSUNDEEP
regexexcelwolfram-mathematica
Soviras
Soviras

Reputation: 53

Converting excel formula to wolfram mathematica using regex

I'm importing Excel files and extracting the formulas in these files to perform various tests and calculations with them that Excel can't do. However, to do that, I need to convert Excels functions to something that Mathematica can use. All of these formulas are very large (on average 200+ pages), so manually converting isn't an option. That brings me to my current problem. Some of the formulas I need to convert contain parentheses within the Excel functions, resulting in situations like this:

IF(IF((A2+B2)*(C1+2^D1)<>7,"A","B")="A",C8,D8)*2/IF(G17*G2=G1,IF(EXP(K9)=K10,K11,K12))*EXP(IF(H22+H23=213,A1,B1))

Here I would need to convert all the () from the IF and EXP functions to [] without changing parts like (A2+B2). The goal is to convert it to:

IF[IF[(A2+B2)*(C1+2^D1)<>7,"A","B"]="A",C8,D8]*2/IF[G17*G2=G1,IF[EXP[K9]=K10,K11,K12]]*EXP[IF[(H22+H23)=213,A1,B1]]

Is there a regex that can match these situations?

Upvotes: 5

Views: 687

Answers (1)

Kuba
Kuba

Reputation: 791

string = "IF(IF((A2+B2)*(C1+2^D1)<>7,\"A\",\"B\")=\"A\",C8,D8)*2/IF(\
G17*G2=G1,IF(EXP(K9)=K10,K11,K12))*EXP(IF(H22+H23=213,A1,B1))"

What about this:

ClearAll@rectBrackets;

rectBrackets := StringReplace[ #, 
    (f_?UpperCaseQ ~~ fr : LetterCharacter ..) ~~ 
    "(" ~~ Shortest[content__] ~~ ")" /; (
        StringCount[content, ")"] == StringCount[content, "("]
    ) :> (
        f <> ToLowerCase[fr] <> "[" <> rectBrackets @ content <> "]"
    )
] &;

operators = StringReplace[#, {"=" -> "==", "<>" -> "!="}] &;

And now let's use them:

rectBrackets @ operators @ string

"If[If[(A2+B2)*(C1+2^D1)!=7,\"A\",\"B\"]==\"A\",C8,D8]*2/If[G17*\
G2==G1,If[Exp[K9]==K10,K11,K12]]*Exp[If[H22+H23==213,A1,B1]]"

 ToExpression @ %

enter image description here

Upvotes: 5

Related Questions