Regular expression to match Excel Formula

0
votes

In one of my problems to solve, I do have to substitute my range values in an Excel Formula with their equivalent cell references, i.e., for example, if I have an Excel Formula:

= Sum(F10:F14)

I have to replace it with:

= Sum(F10,F11,F12,F13,F14).

I am trying to match the regular expressions which helps me find

  1. if an Excel Formula has been defined with a pattern which includes: 'Sum' and a ':'
  2. To extract the cells which are included in the range, as in F10:F14

Somehow, I am stuck at Point 1 :-)

For, this I created the following pattern:

Regex formulaMatcher = new Regex(@"=*SUM\([\w]*[\w,]*[\w]+:[\w]+[\w]*[\w,]*\)*");
foreach (Match m in formulaMatcher.Matches("=SUM(F55,F151:F159)"))
    m.Value.Dump();

Now, the above pattern is working perfectly for the formulas:

        // =SUM(F15,F25,F31:F35)
        // =SUM(F10:F12,F26,F31)
        // =SUM(F45,F55,F61:F63,F40)
        // =F14-SUM(F16:F17)
        // =SUM(F35:F37)
        // =SUM(F10:F13)-F11
        // =SUM(F27:F29)/3
        // =F19-F21+SUM(F22:F23)
        // =ROUND(F43-SUM(F23:F42),2)
        // =SUM(F174:F178)+F134+F120+F97

But it is failing for: SUM(F81:F89,F105:F113,F128:F135), i.e., for cases involving multiple range operators within the formula

Please assist me in finding out how I could achieve the above result?

Cheers, Anil

2
excel
I presume this is homeowork - else it is a very strange question - brettdj

2 Answers

1
votes

At first, I'd suggest using a simpler regex, like:

Regex formulaMatcher = new Regex(@"=*SUM\(.*?[\w]+:[\w]+.*?\)*");

(I don't know if excel supports non-greedy quantifiers, if it doesn't, use * instead of *?, will work also - but less efficient)

This will also match SUM with any contents besides intervals, which may or may not be what you want. If it must be justs cells or intervals, the regex will become a lot more complicated, but it's doable: you just have to break it down to those two simple cases or'ed together:

  • one interval, zero or more intervals or cells starting with comma;
    • \w+:\w+(,\w+(:\w+)?)*
  • one cell, zero or more cells starting with comma, an interval starting with comma, zero or more intervals or cells starting with comma).
    • \w+(,\w+)*,\w+:\w+(,\w+(:\w+)?)*

Edit: Nevermind that, you can simplify it to:

  • zero or more cells ending with comma
    • (\w+,)*
  • one interval
    • \w+:\w+
  • zero or more cells or intervals starting with comma
    • (,\w+(:\w+)?)*

Result:

Regex formulaMatcher = new Regex(@"=*SUM\((\w+,)*\w+:\w+(,\w+(:\w+)?)*\)*");

P.S. I'm unsure whether or not you have to escape commans or colons.

1
votes

Excel lets you have blocks of cells and single cells, stemming for @mgibsonbr 's answer I can up with :

^=SUM\((\w+\d+)((:\w+\d+)|(,\w+\w+)*)((,\w+\d)((:\w+\d+)|(,\w+\w+)*))*\)$

This also makes sure you only have two cells for a range...ie. not a2:a3:a4