Excel ISTEXT alternative?

Question

I am currently building a numberplate checker on an excel spread sheet that will determine if the letters and numbers of the numberplate are in the correct places and are valid.

The 3 criteria I have are if the numberplates are in on of these formulas:
(I have represented a number as 1 and a letter as A)

AAA111A
A111AAA
AA11AAA

The ultimate objective is for the program to ask the question "Look at these number plates, do they follow a format as shown above."

So far I have only been able to check to see if I have numbers in certain places, however I cannot specify the characters A - Z when trying to do a search function from the left, right and centre.

=ISNUMBER(--MID(A3,1,3))

If I wanted to search within a cell for example, the first character, is it a letter a-z, return true or false? How would I go about doing this?

An example in this instance might be:

DJO148R

The formula

=ISNUMBER(--MID(A5,4,3))

This would turn back as true because the 4th character is a number and so are the next 2.

With the same numberplate, how do I change it to search for letters rather than numbers within the numberplate?

Answer 1

Here is a simpler RegEx implementation. Make sure you include references to Microsoft VBScript Regular Expressions 5.5. This will go in a new inserted module

 Function PlateCheck(cell As Range) As Boolean
    Dim rex As New RegExp
    rex.Pattern = "[A-Z][0-9|A-Z][0-9|A-Z][0-9|A-Z][0-9|A-Z][0-9|A-Z][A-Z]"
    If rex.Test(cell.Value) Then
        PlateCheck = True
    Else
        PlateCheck = False
    End If
End Function

Answer 2

As per the guys comments, here's how you do it with regex:

Make sure to include MS VB regular expressions 5.5 as a reference. To do that, in your VBA IDE, go Tools, Reference and then look the regex reference.

Then Add this in a new module:

Function VerifyLicensePlate(ip As Range) As String
    Dim regex As New RegExp
    Dim inputstr As String: inputstr = ip.Value

     With regex
        .Global = True
        .IgnoreCase = True
    End With

    Dim strpattern(2) As String

    strpattern(0) = "[A-Z][A-Z][A-Z][0-9][0-9][0-9][A-Z]"
    strpattern(1) = "[A-Z][A-Z][0-9][0-9][A-Z][A-Z][A-Z]"
    strpattern(2) = "[A-Z][0-9][0-9][0-9][A-Z][A-Z][A-Z]"

    For i = 0 To 2
        regex.pattern = strpattern(i)
        If regex.Test(inputstr) Then
            VerifyLicensePlate = "Match"
            Exit Function
        Else
            VerifyLicensePlate = "No match"
        End If
    Next
End Function

Output:

Answer 3

Occam's Razor would suggest,

=NOT(ISNUMBER(--MID(A5,4,3)))

... or,

=ISERROR(--MID(A5,4,3))

Answer 4

Here's a version that uses late-binding, so no need to set a reference. IT is case insensitive, as that seemed to be implied in your question, but that is easily changed.

Option Explicit
Function MatchPattern(S As String) As Boolean
    Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\b(?:[A-Z]{3}\d{3}[A-Z]|[A-Z]{2}\d{2}[A-Z]{3}|[A-Z]\d{3}[A-Z]{3})\b"
    .ignorecase = True
    MatchPattern = .test(S)
End With
End Function

But, as pointed out by G Serg, you don't really need regex for this:

Option Explicit
Option Compare Text  'Case Insensitive
Function MatchPattern(S As String) As Boolean
    Const S1 As String = "[A-Z][A-Z][A-Z]###[A-Z]"
    Const S2 As String = "[A-Z]###[A-Z][A-Z][A-Z]"
    Const S3 As String = "[A-Z][A-Z]##[A-Z][A-Z][A-Z]"

MatchPattern = False
If Len(S) = 7 Then
    If S Like S1 Or _
        S Like S2 Or _
        S Like S3 Then _
    MatchPattern = True
End If
End Function

Here is a rather complicated formula that seems to match your specifications:

=AND(LEN(A1)=7,
OR(MMULT(--(CODE(MID(A1,{1,2,3,4,5,6,7},1))>64),--(TRANSPOSE(CODE(MID(A1,{1,2,3,4,5,6,7},1))<91)))={4,5}),
CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91,
CODE(RIGHT(A1,1))>64,CODE(RIGHT(A1,1))<91,
ISNUMBER(-MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"0123456789")),
7-MMULT(--(CODE(MID(A1,{1,2,3,4,5,6,7},1))>64),--(TRANSPOSE(CODE(MID(A1,{1,2,3,4,5,6,7},1))<91))))))

• Ensure we have only seven characters
• The OR(MMULT... function counts the number of letters and returns TRUE if four or five.
• Check to make sure first and last character is a letter
• There should remain a consecutive string of either two or three digits (seven less the number of letters)
  • If you want to make the formula case insensitive, replace the instances of A1 with UPPER(A1)

I think the UDF solution is better.