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.
- 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.
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.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”.
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.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
Also Read: Excel VBA - Copy Sheet To Another Workbook
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.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: 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.
Excel VBA Check – IF String Contains – Only Letters
- Games VCF Countif Record Macro Timer in Excel Task Manager print screen HTML Table Import Data Thumbnails Text to Speech TTS Candy Crush Block Apps in FaceBook C++ Programing Machine learning AI bulk email Excel VBA vba color index vba color codes Android social media Twitter Google+ Google Adsense Tips bitcoin Calendar in Excel outlook vba mass email Data Mining Excel Macro Facebook WhatsApp python Wordpress