CODEWITHSUNDEEP
excelvbavariablessplitisnumeric
Davey
Davey

Reputation: 35

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.

Upvotes: 3

Views: 985

Answers (2)

JvdV
JvdV

Reputation: 75850

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.

Upvotes: 3

Variatus
Variatus

Reputation: 14383

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

Upvotes: 0

Related Questions