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.
- Verify if string only has Alphabets – IsAlphabet
- Verify if Alphanumeric – IsAlphaNumeric
- 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”.
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.
- Stackoverflow discussion on how to find if a string has only letters – blog topic is here.