Although Excel itself is a great tool for automating the work that we would usually do manually, VBA is even better and more effective.
To show its usefulness, we will present the code in VBA that we can use to check if we have any special characters in our string in Excel.
Checking If String Has Special Characters with Formula
Since VBA can be observed as a backend for all things related to Excel it can, of course, be used to create new formulas that can be used when needed.
We will create such a formula to check if the string in our workbook has a special character. In our formula, everything that is not either number or a letter from the English alphabet will be considered a special character.
The text on which we are going to use our function is random, and looks like this:
We will go to our VBA module by clicking the combination of ALT + F11. Once there, we will right-click anywhere on the left window and select Module:
In the module window, we will input the following formula:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Function SpecialCharacters(text As String) As Boolean Dim f As Long Dim ee As String For f = 1 To Len(text) ee = Mid(text, f, 1) Select Case ee Case "0" To "9", "A" To "Z", "a" To "z", " " SpecialCharacters = FALSE Case Else SpecialCharacters = TRUE Exit For End Select Next End Function |
The formula will look like this in the module:
Explaining the Formula
First thing first, we define our code as a function by simply writing “Function”. Then we name it to be “Special characters” (this is the name that we will always use to call our function). In parenthesis, we define the arguments for our function. In our case, we need to check a string, so we will define one argument as a string.
We finally declare our formula to be a Boolean, meaning that it will return TRUE or FALSE values:
|
1 |
Function SpecialCharacters(text As String) As Boolean |
Then we declare two variables, “f” as long (for a number) and “ee” as a string (for a string):
|
1 2 |
Dim f As Long Dim ee As String |
We are using For Loop to go through the characters in our string. The MID function gives us characters one by one and stores the value in the “ee“ variable:
|
1 2 |
For f = 1 To Len(text) ee = Mid(text, f, 1) |
For the next and the main part, we are using the Select Case statement to verify if our “ee” variable contains the defined values (number from 0 to 9, letters from a to z, letters from A to Z, and blank space as well).
If it does, then we return FALSE as a result of our function. If any other character is detected, we would be returned with the value TRUE, because in that case, we would have a character that we defined as special for this exercise:
|
1 2 3 4 5 |
Select Case ee Case "0" To "9", "A" To "Z", "a" To "z", " " SpecialCharacters = FALSE Case Else SpecialCharacters = TRUE |
For the final part, we exit our For Loop, we end the Select Case, and then we put Next to go and check the next character in our string.
When all of the characters in our string are checked, we exit the function.
We will save our file as Macro-Enabled Workbook, save the code, and then get back to our worksheet.
To show it in our example, we will go to the cell B1 and then insert our formula:
It is clear that now we have the function called “SpecialCharacters” at our disposal.
We will drag our formula till row number 10, and we will have the following table:
As seen, we will now have a TRUE statement next to the string where we do have a special character. In the strings for which we do not have special characters, we will have a FALSE as a result.