VBA Check If String is Alphanumeric or Not
There is no string operator or function in VBA to check if a field has only alphabets.
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.
- Find Alphabets – IsAlphabet
- Fining Alphanumeric – IsAlphaNumeric
- 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.
Function to check if String has Letter
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.
Check if Character is Alphabet or NotVisual Basic12345678910Function IsAlphabet(inpChar As String) As BooleanDim 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 notIsAlphabet = Asc(chkChar) > 64 And Asc(chkChar) < 91End 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”.
Function to Check if Character is Alphanumeric or not
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.VBA to Check is Character is AlphanumericVisual Basic12345678910Function IsAlphaNumeric(inpChar As String) As BooleanDim 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 NumericIsAlphaNumeric = (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 takes String as input and other 2 functions take input as single character.Remove Invalid Characters from StringVisual Basic123456789101112Function TrimNonAlphaNumeric(inpStr As String) As StringDim chkStr As String, outStr As String, idx As DoubleFor idx = 1 To VBA.Len(inpStr)chkStr = VBA.Mid(inpStr, idx, 1)If chkStr = " " Or IsAlphaNumeric(chkStr) ThenoutStr = outStr & chkStrEnd IfNext idxTrimNonAlphaNumeric = outStrEnd Function
Note: To use this function to check if string is alphabet or not, in your code, copy all three functions and paste it. Because, I have called one function from other. If you copy only the last function, then VBA will give you error message.
Previous Post: Excel Import Data from another Workbook
Next Post: VBA to Create Email List using Excel – Distribution List
Function in VBA to Check if string contains only Letters