如何找到与Excel公式匹配的正则表达式？

Question

我有一个Excel公式：

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

我正在尝试匹配正则表达式，这有助于我找到

1. 如果用以“SUM”

开头的模式定义了Excel公式

1. 如果Excel公式有2‘间接’，2‘行’和':'

“N”和“P”的意思是科尔。“ROW()”后面的“-19”这样的数字只是随机数字。

请帮助我找出上述两个公式中的正则表达式。

Answer 1

一个可能的匹配是：

/^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"