Excel VBA Check – IF String Contains – Only Letters

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)”. In that case, use this function that can get a character and 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. Find Alphabets – IsAlphabet
  2. Fining Alphanumeric – IsAlphaNumeric
  3. Remove special characters and Symbols from String – TrimNonAlphaNumberic

We use this kind of tiny code when we need to remove only the special characters and 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 this function inside your VBA code and also as a worksheet function.

Please remember this function only takes a character as input, though we have defined it as string.

Function IsAlphabet(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
    IsAlphabet = 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.

Leave a Reply