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

Question

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.

I recommend to do this with [Regular Expressions](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops): You could use a pattern like this [`^[A-Za-z]{4} [0-9]+$`](https://regex101.com/r/eiSG7C/1/) to validate your strings. The issue is that `isNumeric` can only tell if **the whole** (sub)string is numeric or not. It cannot tell if it *contains* numbers or not. • If you don't want to use RegEx then you need to check every single character if it is a number or not, or use `instr` to test if a string contains any of `0-9`. — Pᴇʜ, Jan 20 '20 at 08:14
As per @Pᴇʜ, `RegEx` is a fantastic way to check these, but there is a build-in alternative with the `Like` operator. — JvdV, Jan 20 '20 at 08:46
@Pᴇʜ Thank you, gave this post a thorough read and a few honest attempts before reaching for the nearest deity. I find that the `Like` option is far easier for me to wrap my head around, as I sort of understand what is happening in the code and why. — Davey, Jan 20 '20 at 11:04

JvdV · Accepted Answer · 2020-01-20T13:33:21.130

3

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.

edited Jan 20 '20 at 13:33

answered Jan 20 '20 at 08:46

JvdV

70,606
8
39
70

1

Uhh nice, I didn't know that the `Like` operator accepts basics RegEx like syntax like `[A-Z]`. Good to know and for simple checks probably easier to implement than RegEx. – Pᴇʜ Jan 20 '20 at 08:52
Rather suprising right! @Pᴇʜ. [Here](https://analystcave.com/vba-like-operator/) are some nice examples. It however doesn't tell you, you can combine these ranges. So even `[A-Za-z0-9]` would work (and varieties like `[A-Ca-d3-7]`). – JvdV Jan 20 '20 at 09:02
Tested these. They work! But the string doesnt post when I add spaces after the `[A-Z]` or between the `#`. When I remove those, the number does get printed to the sheet, but as QWER1234567 instead of QWER 123456 7. – Davey Jan 20 '20 at 11:00
@Davey, could you edit your original question and give some examples? Sounds like you need a `Application.Trim` before running a comparison. And if there can be a arbrirary amount of spaces, then use the first solution in my post but loop through the returned array instead. – JvdV Jan 20 '20 at 11:49
@JvdV I edited the question, I hope this is what you meant. I am afraid I dont yet understand how to create loops myself yet. – Davey Jan 20 '20 at 13:27
@Davey, right, you might benefit from a `Application.Substitute` then =) will update the answer. – JvdV Jan 20 '20 at 13:29

score 0 · Answer 2 · answered Jan 20 '20 at 09:25

In this solution approval of the contract number format is provided by a function that returns True if the number is good, or False. If the number isn't good the function tells what's wrong with it. If found acceptable the calling procedure gets on with the program. Note that the function accommodates missing or extra spaces and converts lower case letters to upper.

Option Explicit

Private Sub TestConNumber()

    Dim ConNr As String

'    ConNr = Me.txtContainer.Value
    ConNr = "QAAK 781234 x"
    If GetConNumber(ConNr) Then
        MsgBox "The Contract number is " & ConNr
    End If
End Sub

Private Function GetConNumber(ConNr As String) As Boolean
    ' return Not True if incorrect

    Dim Fun As Boolean                     ' function return value
    Dim Nr As String
    Dim Msg As String
    Dim Arr(1 To 3) As String

    Nr = UCase(Replace(ConNr, " ", ""))
    If Len(Nr) = 11 Then
        Arr(1) = Left(Nr, 4)
        If Arr(1) Like "[A-Z][A-Z][A-Z][A-Z]" Then
            If IsNumeric(Right(Nr, 7)) Then
                Arr(2) = Mid(Nr, 2, 6)
                Arr(3) = Right(Nr, 1)
                ConNr = Join(Arr)
                Fun = True
            Else
                Msg = "The last 7 digits must be numbers."
            End If
        Else
            Msg = "The first 4 characters must be non-numeric"
        End If
    Else
        Msg = "Input must have 11 characters"
    End If

    If Not Fun Then
        MsgBox Msg, vbExclamation, "Wrong input"
    End If

    GetConNumber = Fun
End Function

I tried this but I got an compilation error 'Comments are only allowed after `End Sub`, `End Function` or `End Property`'. (The `Option Explicit` was highlighted.) Also do I add this code to the form or do I need to add this to a separate module? — Davey, Jan 20 '20 at 11:29
*Option Explicit* must be at the very top of the code sheet, above any declarations of procedures. The function call should be in your existing procedure: *If GetConNumber(ConNr) Then* at the top and *End If* before the procedure's end (avoid interrupting the flow of your code with *Exit Sub*s). The function *GetConNumber* should be below the calling procedure but you can also place it in a standard code module. If it's not in the same module as the calling procedure remove the word "Private" from its declaration to make it *Public*. — Variatus, Jan 20 '20 at 11:37

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

2 Answers2