Excel VBA to Check If String Contains Letters: 3 Easy Ways

VBA Breakdown:

Dim Rng As Range
Set Rng = Range("D5:E14")

• Define a Range type variable “Rng”.
• Set the D5:E14 range as the value for that variable.

For i = 1 To Rng.Rows.Count
    Text = Rng.Cells(i, 1).Value

• Run a For Loop for each row of the “Rng” range.
• The code sets the cell’s value in the first range as the value of the “Text” variable when i=1. At every iteration, the code goes down the first column and assigns 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, the letter is an alphabetic character.
• Rng.Cells(i, 2) = True: If the condition in the previous step is True, the letter is an alphabetic character. In this case, 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 contains no alphabetic characters.

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")

• Declare a Range type variable “Rng”.
• 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 range’s columns using the variable “j.” It starts at 1 and increases 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 moves 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, it will prompt us to write down one of the following three strings: Accounting, Marketing, or 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.

VBA Breakdown:

Function CHECKLETTERSASK(Str As String) As Boolean

• 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 passed into the function.
• CHECKLETTERSASK = False: 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, the code will set the value of the UDF function to True and exit the function.

VBA Breakdown:

Public Function CHECKLETTERSLIKE(Str As String) As Boolean

• 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 passed into the function.
• CHECKLETTERSLIKE = False: 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, the function’s value will be set to True. Then, the code will exit the function.