excel - 检查单元格内容是否匹配格式的公式，正则表达式？

Question

我必须检查单元格的内容是否与特定格式匹配。
如果是这种情况，那么我必须将 Yes else No 放在 Result 列中。
格式如下:REFXXXX(,REFXXXX,...)。
括号内的内容是可选的，X 应该是一个数字序列(最少 8 个，最多 12 个)。
数据示例

Reference                              | Result
REF12324567                            | Yes
REF1                                   | No
                                       | No
Some text                              | No
REF1234567,REF789654123,REF741258963   | Yes

使用正则表达式，它可以是 REF(\d{8,12})((,REF(\d{8,12}))*)但是我不知道是否可以使用正则表达式作为 Excel 公式。
你能解释一下我该怎么做吗？
谢谢，问候

Answer 1

没有直接等效的工作表函数。但是，您可以使用 FILTERXML()作为某种替换功能，将字符串“拆分”为不同的元素并使用一些 xpath 来验证每个元素。
B1 中的公式:

=IF(AND(ISERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[not(starts-with(., 'REF') and string-length() >=11 and string-length() <=15 and translate(., 'REF', '')*0=0)]")),A1<>""),"Yes","No")

"<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>" - 创建一个有效的 xml 字符串。

//s - 返回 s 节点，其中:

not(starts-with(., 'REF') - s节点做不是 以“REF”开头，并且；

string-length() >=11 and string-length() <=15 - 字符串的总长度在 11-15 之间，并且；

translate(., 'REF', '')*0=0 - 如果删除“REF”，则余数为数字。

如果在此验证后没有可以返回的 s 节点 FILTERXML()将抛出一个我们用 ISERROR() 捕获的错误嵌套在 IF() 中返回"is"或“否”。

请注意 FILTERXML()是区分大小写的。

请注意，您的第 4 个样本未验证。

请注意，您也可以考虑构建自己的 UDF (用户定义函数)通过​​使用 VBA 和正则表达式对象。这样你就可以使用你所知道的正则表达式。有关该主题的信息，请参阅 this伟大的来源。

如果您想了解更多关于 FILTERXML() 的使用我想将您重定向到: Excel - Extract substring(s) from string using FILTERXML