How do I make IsNumeric check the whole string/variable instead of just the first character?

I am using IsNumeric to check if a part of a variable are numbers or not. Unfortunately it only seems to check the first character of the string part instead of the whole bit.

It currently accepts i.e. Q123 1234567 and QWER 1QWERTYR (and other varients of that). While I need the first 4 characters to be all letters and the others to be all numbers.

I have no idea what I am missing still. Please add extra comments if at all possible, my understanding of vba is below basic still.

Dim ConNr As String
Dim Space As String
Dim Four As String
Dim Six As String
Dim One As String
Dim Container As String

ConNr = Me.txtContainer.Value
Space = " "
Four = Left(Me.txtContainer.Value, 4)
Four = UCase(Four)
Six = Mid(Me.txtContainer.Value, 5, 6)
One = Right(Me.txtContainer.Value, 1)

'Check if all 4 are letters
If IsNumeric(Four) = True Then
    MsgBox "First 4 need to be letters."
    Me.txtContainer.SetFocus
    Exit Sub
Else
    'MsgBox "Four Letters " + Four

'Check if 6 characters are numbers
If IsNumeric(Six) = False Then
    MsgBox "4 Letters followed by 6 numbers."
    'MsgBox "These Six " + Six
    Me.txtContainer.SetFocus
    Exit Sub
Else
    'MsgBox "Six Numbers " + Six

'Last number is number
If IsNumeric(One) = False Then
    MsgBox "Last character needs to be a number."
    Me.txtContainer.SetFocus
    Exit Sub
Else
    'MsgBox "Last Number " + One
    ConNr = Four & Space & Six & Space & One
    Container = ConNr
End If
End If
End If

Edit based on JvdV

When I tried "[A-Za-z][A-Za-z][A-Za-z][A-Za-z] ###### #" the output was empty.

I dont want to force the user to use the correct format. (Caps, spaces.) But the 4 letters/7 numbers are required.


Dim ConNr As String: ConNr = Me.txtContainer.Value


If ConNr Like "[A-Za-z][A-Za-z][A-Za-z][A-Za-z]#######" Then ‘Without spaces, else it doesn’t post.
Container = UCase(ConNr)
    Else
    MsgBox "YOU FAILED."
    Me.txtContainer.SetFocus
    Exit Sub

End If

‘Output should become ASDF 123456 7. Currently gives me ASDF1234567.

Solution

As per my comment, hereby a simple sample code to demonstrate the use of the Like operator:

Sub Test()

Dim str As String: str = "QWER 1234567"
Dim arr As Variant: arr = Split(str, " ")

If arr(0) Like "[A-Z][A-Z][A-Z][A-Z]" And IsNumeric(arr(1)) Then
    Debug.Print str & " is passed!"
End If

End Sub

Btw, if you want to allow for upper- and lowercase you could use: [A-Za-z][A-Za-z][A-Za-z][A-Za-z]

Edit

If you looking for a pattern of 4 alphabetic chars, then a space, then 6 digits, you can even do something more simplistic:

Sub Test()

Dim str As String: str = "QWER 123456"

If str Like "[A-Z][A-Z][A-Z][A-Z] ######" Then
    Debug.Print str & " is passed!"
End If

End Sub

Extend the expression if you want to include another space/digit. You are talking about:

"ConNr = Four & Space & Six & Space & One"

So [A-Z][A-Z][A-Z][A-Z] ###### # would work for you in that case.

As per your comment, you don't want to force a specific format on the users, as long as they have 4 alpha and 7 numeric characters in their string. In any form. So I figured, since there are so many places to put spaces, it's best to get rid of them using Application.Substitute. Your code might look like:

If Application.Substitute(Me.txtContainer.Value, " ", "") Like "[A-Za-z][A-Za-z][A-Za-z][A-Za-z]#######" Then
    Debug.Print str & " is passed!"
End If

If you don't want to forec upper cases but want to return it nonetheless then use the UCase function to cap the whole string at once!

Debug.Print UCase(Application.Substitute(Me.txtContainer.Value, " ", ""))

It's hard to hide the fact that this resembles RegEx a lot.