CODEWITHSUNDEEP
regexexcelvbaexcel-2010
Carol.Kar
Carol.Kar

Reputation: 5355

Matching function of a regular expression in 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?

Upvotes: 2

Views: 12430

Answers (2)

Alex K.
Alex K.

Reputation: 175766

You could use the built-in Like method;

if "DE0006231009" like "[A-Za-z][A-Za-z]##########" then ...

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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

Upvotes: 4

Related Questions