Excel中正则表达式的匹配功能? [英] Matching function of a regular expression in excel?

问题描述

我的工作表中有几个单元格，其中包含 ISIN .

I have several cells in my sheet which contain an ISIN.

以下是一个ISIN的示例:DE0006231004

Here is an example of an ISIN: DE0006231004

我创建了一个与ISIN匹配的正则表达式: ^[a-zA-Z]{2}[0-9]{10}$

I have created a regular expression which matches the ISIN: ^[a-zA-Z]{2}[0-9]{10}$

我想在我的单元格上匹配此正则表达式，如果匹配则为1，否则为0.

I want to match this regex on my cell and give a 1 if it matches otherwise a 0.

有可能通过函数吗?

推荐答案

以下功能将满足您的需求.如果字符串不匹配，则返回0(零)，如果字符串与pattern匹配，则返回1(一).

The following function will do what you need. It will return either 0 (zero) if string doesn't match or 1 (one) if the string matches to pattern.

Function MatchISIN(ISIN As String)

    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")

    regEx.Pattern = "^[a-zA-Z]{2}[0-9]{10}$"
    regEx.IgnoreCase = True
    regEx.Global = True

    Dim Matches As Object
    Set Matches = regEx.Execute(ISIN)

    MatchISIN = Matches.Count

End Function

这篇关于Excel中正则表达式的匹配功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！