In this article, we will discuss VBA codes to check if a string contains letters. Excel users often need to look for letters in a string. Excel does not have any default function to check whether a string contains letters or any specific letter. So, this article will allow users to look for their desired letters or letter from a string or find out whether the string contains any letter at all. In this case, we will use two VBA subroutines and two VBA user-defined functions to check if a string contains letters or not.
Excel VBA to Check If String Contains Letters: 3 Easy Ways
In this article, we will discuss 3 ways of using VBA codes to check if a string contains letters. Here, we have a dataset that contains the information of some employees. If you look closely, some IDs of the “ID” column contain letters and some do not. We will use VBA codes to find out which IDs contain letters. We will also write another VBA code to look for specific letters. Finally, we will declare two user-defined functions to check if a string contains letters.
1. VBA Code to Check If String Contains Any Letter
In this method, we will go through some strings and check if they contain any letters at all. Every character has a unique ASCII code and the computer recognizes them with that code. The upper case alphabet’s codes start from 65 and end at 90. The Lower Case alphabets codes range from 97 to 122 in the ASCII table. We will utilize these codes to identify letters from a string. We will use the following code to do so.
Sub AnyLetter()
Dim Rng As Range
Set Rng = Range("D5:E14")
For i = 1 To Rng.Rows.Count
Text = Rng.Cells(i, 1).Value
For j = 1 To Len(Text)
letter = Asc(Mid(Text, j, 1))
If (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122) Then
Rng.Cells(i, 2) = True
Exit For
Else
Rng.Cells(i, 2) = False
End If
Next j
Next i
End Sub
VBA Breakdown:
Dim Rng As Range
Set Rng = Range("D5:E14")- Here, we define a Range type variable “Rng”.
- Then, we set the D5:E14 range as the value for that variable.
For i = 1 To Rng.Rows.Count
Text = Rng.Cells(i, 1).Value- We run a For Loop for each row of the “Rng” range.
- Then, the code sets the value of the cell in the first range as the value of the “Text” variable when i=1. At every iteration, the code will go down the first column and assign the first cell for each row as the value of the “Text” variable.
For j = 1 To Len(Text)
letter = Asc(Mid(Text, j, 1))
If (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122) Then
Rng.Cells(i, 2) = True
Exit For
Else
Rng.Cells(i, 2) = False
End If
Next j
Next i- For j = 1 To Len(Text): This line starts a loop that iterates from 1 to the length of the “Text” string. It uses the variable j as the loop counter.
- letter = Asc(Mid(Text, j, 1)): This line retrieves a single character from the “Text” string at position j using the Mid function. The Asc function is then used to get the ASCII value of that character, which is stored in the variable letter.
- (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122): This is a condition that checks if the ASCII value of a letter falls within the range of uppercase letters (A-Z: ASCII 65-90) or lowercase letters (a-z: ASCII 97-122). If the condition evaluates to True, it means that the letter is an alphabetic character.
- Rng.Cells(i, 2) = True: If the condition in the previous step is True, it means that the letter is an alphabetic character. In this case, the value True is assigned to the cell in the second column (Column 2) of the “Rng” range at the current row i. This indicates that the “Text” string contains at least one alphabetic character.
- Rng.Cells(i, 2) = False: If the condition in step 3 is False, it means that “letter” is not an alphabetic character. In this case, the value False is assigned to the cell in the second column of the “Rng” range at the current row i. This indicates that the “Text” string does not contain any alphabetic characters.
As we run this code by launching VBA Macro editor, the code will write True beside the IDs that contain letters and False that do not.
2. Check If String Contains Specific Letters
In this instance, we will use a VBA code to check a string contains specific letters. This will allow users to find a particular sub-string from a string. We will use the InStr function in this case. We will use the following code to do so.
Sub SpecificLetters()
Dim Rng As Range
Set Rng = Range("B5:D14")
User = InputBox("Please write down the department that you want to find i.e:Finance/Marketing/Accounting")
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Text = Rng.Cells(i, j)
If InStr(Text, User) > 0 Then
Rng.Cells(i, 1).Offset(0, Rng.Columns.Count) = "Found"
End If
Next j
Next i
End Sub
VBA Breakdown:
Dim Rng As Range Set Rng = Range("B5:D14")User = InputBox("Please write down the department that you want to find i.e:Finance/Marketing/Accounting")
- Here, we declare a Range type variable “Rng”.
- Then, we set the B5:D14 range as the value for that variable.
- The code prompts users to type whether “Finance” or “Marketing” or “Accounting” to look for related values from the dataset.
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Text = Rng.Cells(i, j)
If InStr(Text, User) > 0 Then
Rng.Cells(i, 1).Offset(0, Rng.Columns.Count) = "Found"
End If
Next j
Next i- The outer loop iterates over the rows of the range using the variable “i”. It starts from 1 and goes up to the number of rows in the range, which is obtained by Rng.Rows.Count.
- The inner loop iterates over the columns of the range using the variable “j”. It starts from 1 and goes up to the number of columns in the range, obtained by Rng.Columns.Count.
- The line Text = Rng.Cells(i, j) retrieve the value of the current cell in the range, located at row “i” and column “j”, and assigns it to the variable “Text”.
- The line If InStr(Text, User) > 0 Then checks if the value of the current cell contains the text specified by the variable “User”. InStr is a function that searches for one string within another. If the text is found, the condition evaluates to true.
- If the condition in the previous step is true, the line Rng.Cells(i, 1).Offset(0, Rng.Columns.Count) = “Found” is executed. This line assigns the value “Found” to a cell in the same row as the current cell but located in the last column of the range. The Offset method is used to move a certain number of cells from a reference cell, in this case, the cell at column 1 of the current row.
- As we run the code, the code will prompt us to write down any of the following 3 strings, Accounting, Marketing, and Finance.
- As we write any of the three, the code will find it in the dataset and write “Found” at the last cell of that row.
Read More: Excel VBA: If Cell Contains Value Then Return a Specified Output
3. User Defined Function to Check If String Contains Letters
User Define Functions are customized functions that users create to perform certain tasks. These functions are similar to the default functions of Excel like the SUM function, the COUNT function, etc. In this method, we will define two new functions that check if a string contains letters or not.
3.1. User-Defined Function Using Asc Function
In this instance, we will create the CHECKLETTERSASK function that uses the Asc function to check whether a string contains letters or not. The Asc function returns the ASCII number of a character. We will use this function inside our custom function to check if the string contains any letter or not. The code for this UDF is as follows,
Function CHECKLETTERSASK(Str As String) As Boolean
Dim i As Integer
For i = 1 To Len(Str)
CHECKLETTERSASK = False
letter = Asc(Mid(Str, i, 1))
If (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122) Then
CHECKLETTERSASK = True
Exit Function
End If
Next i
End Function
VBA Breakdown:
Function CHECKLETTERSASK(Str As String) As Boolean- In this line, we define the CHECKLETTERSASK function which will return a Boolean value.
- The argument of the function will be a String type variable.
For i = 1 To Len(Str)
CHECKLETTERSASK = False
letter = Asc(Mid(Str, i, 1))
If (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122) Then
CHECKLETTERSASK = True
Exit Function
End If
Next i- For i = 1 To Len(Str): The function will run a For Loop from 1 to the length of the argument that will be passed into the function.
- CHECKLETTERSASK = False: Here, we initialize the return value of the function as False.
- letter = Asc(Mid(Str, i, 1)): The Mid function will extract each character from the “Str” argument at each iteration of the For Loop. The Asc function will get the ASCII code for that character and store it in the “letter” variable.
- If (letter >= 65 And letter <= 90) Or (letter >= 97 And letter <= 122): The If statement will check if the value of the “letter” variable is between 65-90 or 97-122 which are the ASCII numbers for alphabets.
- CHECKLETTERSASK = True: If “letter” is an alphabet then the code will set the value of the UDF function to True and exit the function.
- Now, we write the UDF function in the E5 cell by passing in the value in the D5 cell as its argument.
- The function will return True as the argument contains a letter.
- Finally, drag down the cursor to autofill the rest of the cells of the “Check Letters” column.
- The function will give either True or False accordingly.
3.2. User-Defined Function Using Like Operator
In this example, we will declare a UDF that contains the Like operator. The Like operator compares a string with a pattern and determines if they match. It allows you to perform pattern matching and wildcard searches within strings. We will use the following code in the UDF function.
Public Function CHECKLETTERSLIKE(Str As String) As Boolean
For i = 1 To Len(Str)
CHECKLETTERSLIKE = False
letter = Mid(Str, i, 1)
If letter Like "[A-Za-z]" Then
CHECKLETTERSLIKE = True
Exit Function
End If
Next i
End Function
VBA Breakdown:
Public Function CHECKLETTERSLIKE(Str As String) As Boolean- First, we define the CHECKLETTERSLIKE function which will return a Boolean value.
- The argument of the function will be a String type variable.
For i = 1 To Len(Str)
CHECKLETTERSLIKE = False
letter = Mid(Str, i, 1)
If letter Like "[A-Za-z]" Then
CHECKLETTERSLIKE = True
Exit Function
End If
Next i- For i = 1 To Len(Str): The function will run a For Loop from 1 to the length of the argument that will be passed into the function.
- CHECKLETTERSLIKE = False: Here, we initialize the return value of the function as False.
- letter = Mid(Str, i, 1): The Mid function will extract each character from the “Str” argument at each iteration of the For Loop.
- If letter Like “[A-Za-z]”: The “letter” is a variable or expression that contains a single character. Like is the operator used for pattern matching. [A-Za-z] is the pattern enclosed within square brackets. This pattern represents a character range and matches any uppercase or lowercase letter from A to Z.
- CHECKLETTERSLIKE = True: If the “letter” variable contains any alphabet then the value of the function will be set to True. Then, the code will exit the function.
- Now, we write the UDF function in the E5 cell by passing in the value in the D5 cell as its argument.
- The function will return True as the argument contains a letter.
- Finally, drag down the cursor to autofill the rest of the cells of the “Check Letters” column.
- The function will give either True or False accordingly.
Frequently Asked Questions
1. How do I check if a cell contains text in Excel?
We can use the ISTEXT function to verify whether a cell in Excel contains text or not. It returns TRUE if the value in the specified cell is text, and FALSE otherwise.
The syntax of the ISTEXT function is as follows:
ISTEXT(value)2. How do I count if a cell contains text or text?
To count the number of cells in a range that contain text or specific text, you can use the COUNTIF function in Excel. Here’s an example of how you can do that:
Assuming you have a range of cells A1 to A10, and you want to count the number of cells that contain the text “Apple” or any other text:
=COUNTIF(A1:A10, "*")In this example:
- A1:A10: represents the range of cells you want to check.
- “*”: This is the criteria used in the COUNTIF function. The asterisk (*) is a wildcard character that matches any number of characters, so it counts all cells that contain any text.
Download Practice Workbook
You can download the practice book here.
Conclusion
In this article, we have talked about VBA codes to check if string contains letters. This article will get users a ready-made function to check if a string contains letters. This will also allow them to identify strings with letters and manipulate them accordingly.
Related Articles
- Excel VBA: If Statement Based on Cell Value
- VBA IF Statement with Multiple Conditions in Excel
- Excel VBA: Combining If with And for Multiple Conditions
- Excel VBA: Combined If and Or
- Excel VBA Nested If Then Else in a For Next Loop
- Else Without If Error VBA in Excel
- Excel VBA: Check If a File Exists or Not
- Excel VBA: Check If a Sheet Exists
