Check IF VBA String Contains Letters or Alphanumeric

There is no string operator or function that performs VBA check if string contains only letters.

There is worksheet function with name “IsText”. But this function will return “True” even for “IsText( a123)”.

You could use one of these functions that can get a string/character & return whether it is alphabet or not. We have also given a function that can check whether a string has only alphabets or numbers and remove invalid characters.

  1. Verify if string only has Alphabets – IsAlphabet
  2. Verify if Alphanumeric – IsAlphaNumeric
  3. Remove special characters & Symbols from String – TrimNonAlphaNumberic

We use this kind of tiny code when we need to remove only the special characters & symbols from string fields.

VBA Check If String Contains Letters

Add a new module in your VBA project, then copy paste this code. Then you can use one of these function inside your VBA code and also as a worksheet function.

Function IsAlphabet(s As Variant) As Boolean
    IsAlphabet = False
    
    'Check if string only has letters
    If Len(s) > 0 Then IsAlphabet = Not s Like "*[!a-zA-Z]*"
End Function

The below function can be used if a single character is alphabet or not. Though above function works fine, we are adding few more alternate solutions to this problem.

Function IsCharAlphabet(inpChar As String) As Boolean
    Dim chkChar As String
    
    'Convert the character to Uppercase.
    'So that there is no need to do a check for Lower and Uppercase seperately.
    chkChar = UCase(inpChar)
    
    'Check whether input character is Alphabet or not
    IsCharAlphabet = Asc(chkChar) > 64 And Asc(chkChar) < 91
End Function

To use this in Worksheet cells, enter the formula as “=IsAlphabet(1)” or “=IsAlphabet(A1)”. Based on the input the function will return “TRUE” or “FALSE”.

Also Read: VBA Code To Convert String to Date or Number in Excel

VBA Check IF String Contains Letters with Alphanumeric

This is also similar function with some add on feature to above module. In addition to the above code, this function uses VBA IsNumeric function.

Function IsAlphaNumeric(inpChar As String) As Boolean
    Dim chkChar As String
    
    'Convert the character to Uppercase.
    'So that there is no need to do a check for Lower and Uppercase seperately.
    chkChar = UCase(inpChar)
    
    'Check whether input character is Alphabet or Numeric
    IsAlphaNumeric = (Asc(chkChar) > 64 And Asc(chkChar) < 91) Or (VBA.IsNumeric(chkChar))
End Function

Remove Non Alphabet Numeric Characters from String

Apart from removing invalid characters from string, the difference between the above functions and this one is; that, this function in VBA check if string contains Alphanumeric and other 2 functions takes only a single character as input.

Function TrimNonAlphaNumeric(inpStr As String) As String
    Dim chkStr As String, outStr As String, idx As Double
    
    For idx = 1 To VBA.Len(inpStr)
        chkStr = VBA.Mid(inpStr, idx, 1)
        If chkStr = " " Or IsAlphaNumeric(chkStr) Then
            outStr = outStr & chkStr
        End If
    Next idx
    
    TrimNonAlphaNumeric = outStr
End Function

Note: VBA check if string contains letters function can be used in your code, by copying all three functions & paste it in your project or module. Because, I have called one function from other. If you copy only the last function, then VBA will give you error message.

Additional References:

  1. Stackoverflow discussion on how to find if a string has only letters – blog topic is here.

Leave a Reply