3

What I want to match is this regex: ^[a-zA-Z]{2}[0-9]{10}$

For example, how can I test whether the contents of E2 are like [A-Za-z][A-Za-z]##########, and can I wrap that into an IF statement?

Community
  • 1
  • 1
user2051347
  • 1,609
  • 4
  • 23
  • 34
  • 2
    Clarify your question please. Could you put some valid input and output? And should that `IF` return 1 here? – Jerry Jan 08 '14 at 16:48
  • @Jerry if the regex matches in excel I want to five out `TRUE` else `FALSE`. My `E` column contains numbers which match with the regex. – user2051347 Jan 08 '14 at 16:49
  • 2
    Just to check: if E2 has the text `^[a-zA-Z]{2}[0-9]{10}$`, you want to get `TRUE`? – Jerry Jan 08 '14 at 16:53
  • Yep, basically I want to check for such a number `ZAE000006284` – user2051347 Jan 08 '14 at 16:53
  • 1
    I didn't mean the meaning of the regex, I meant the literal text `^[a-zA-Z]{2}[0-9]{10}$`, but your latest comment confirms it. Excel formulas unfortunately cannot handle regex matching. You'll have to use VBA for this and perhaps create a custom function to do that. – Jerry Jan 08 '14 at 16:54
  • I though about using `like`? Isn`t it possible to use this in a proper way? – user2051347 Jan 08 '14 at 16:55
  • 1
    @user2051347 It's not like SQL, there's no `LIKE` function. – Jerry Jan 08 '14 at 16:56
  • 2
    VBA actually does have a `Like` operator which does some basic pattern-matching. Unfortunately I can't find a canonical reference for it. – Charles Wood Jan 08 '14 at 16:58
  • @cyborg how to use `regexp` in an if statement? – user2051347 Jan 08 '14 at 17:00
  • @user2051347 It looks like you have an example `If` statement already; have you tried it? – Charles Wood Jan 08 '14 at 17:03
  • You can follow this answer, and you don't need an if statement: stackoverflow.com/a/11906595/907578 – cyborg Jan 08 '14 at 17:05

2 Answers2

1

Use this:

Function regxMatch(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        M = "Matches '" & Pattern & "'"
    Else
        M = ""
    End If
End Function

Function should be self explanatory!

Carol.Kar
  • 4,581
  • 36
  • 131
  • 264
  • 1
    @user2051347 You might want to rename the function since there's already a function called `Match`. Maybe something like `regexmatch`. – Jerry Jan 08 '14 at 17:13
  • 1
    @Jerry How can I use this function on my vba code? I am posting this as module in the vba editor, however I cannot open the function? – user2051347 Jan 08 '14 at 17:15
  • 1
    @user2051347 Save the module, then go to your sheet. You should be able to use the function `=regxMatch()` Well, I'm on 2007 and it's throwing me an error. I guess this could work on 2010. – Jerry Jan 08 '14 at 17:27
  • Function is not self-explanatory as the `M` output is not returned to `RegxMatch` – brettdj Jan 08 '14 at 22:57
  • 1
    @brettdj pls edit the answer properly. – user2051347 Jan 09 '14 at 09:27
0

You can run a shorter test as such:

Function regexTest(strIn As String, strPattern As String) As Boolean
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    objRegex.Pattern = strPattern
    regexTest = objRegex.test(strIn)
End Function

test code

Sub TestME()
MsgBox regexTest("ZAE000006284", "^[a-zA-Z]{2}[0-9]{10}$") ' False
MsgBox regexTest("ZA0000000628", "^[a-zA-Z]{2}[0-9]{10}$") ' True
End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177