excel中用正则匹配,Excel中正则表达式的匹配功能？

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

Here is an example of an ISIN: DE0006231004

I have created a regular expression which matches the ISIN:

^[a-zA-Z]{2}[0-9]{10}$

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

Is this possible with a function?

解决方案

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