How to find Regular expression to match Excel Formula?-CodePudding

I have an Excel Formula:

SUM(INDIRECT(\"N\"&(ROW()-10-5)):INDIRECT(\"N\"&(ROW()-1)))
SUM(INDIRECT("P"&(ROW()-19)):INDIRECT("P"&(ROW()-1)))

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

if an Excel Formula has been defined with a pattern which starts with ‘SUM’
if an Excel Formula has 2 ‘INDIRECT’ ，2'ROW', and a ':'

The 'N' and 'P' means cols. And the number like '-19' behind 'ROW()' is just random numer.

Please assist me in finding out what regular expressions are to macth above two formulas

CodePudding user response：

One possible match would be:

/^SUM.*?INDIRECT.*?ROW[^:] :INDIRECT.*?ROW/

^SUM         // starts with "SUM"
.*?INDIRECT  // followed by anything up to the first occurrence of "INDIRECT"
.*?ROW       // followed by anything up to the first occurrence of "ROW"
[^:]         // Followed by 1 or more non-colon characters
:INDIRECT    // Followed ":INDIRECT"
.*?ROW       // followed by anything up to the next occurrence of "ROW"