CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Excel VBA - Allow Digits in String

I am using this function to restrict users from entering special characters but I am also looking to allow users to be able to enter only 2 digits (should they want) together with letters. Is this possible?

Public Function IsAlpha(strValue As String) As Boolean
    IsAlpha = strValue Like WorksheetFunction.Rept("[-a-zA-Z ]", Len(strValue)) And _
       Len(strValue) = Len(Application.Trim(strValue))
End Function

Upvotes: 0

Views: 228

Answers (2)

Nathan Sutherland
Nathan Sutherland

Reputation: 1270

If you don't want to load the regex reference library, you can use late binding.

Public Function IsAlpha(strValue As String) As Boolean
    Dim rgx As Object
    Set rgx = CreateObject("vbscript.regexp")

    With rgx
        .Global = False
        .Pattern = "^[A-Z]*[0-9]?[A-Z]*[0-9]?[A-Z]*$"
        .IgnoreCase = True
        .MultiLine = False
        IsAlpha = .Test(strValue)
    End With
End Function

Upvotes: 1

Sam
Sam

Reputation: 5731

Here is a version without regular expressions:

Public Function IsAlpha(strValue As String) As Boolean
    IsAlpha = strValue Like "[-a-zA-Z]" & _
        WorksheetFunction.Rept("[-a-zA-Z 0-9]", Len(strValue) - 1) _
        And _
        Len(strValue) = Len(Application.Trim(strValue)) _
        And _
        Not strValue Like "*[0-9][0-9][0-9]*"
End Function

It looks for a string starting with something else than a number, followed by letters and numbers. After that it checks that there aren't three consecutive numbers.

Having that said - don't do like this. This is the very purpose of regular expresssions.

Upvotes: 0

Related Questions