Here, we have a dataset of some persons’ names. All the names start with Mr. or Ms. Now, our goal is to find whether it is male or female by finding the word “Mr.” or “Ms.”
Method 1: Using a VBA Code with the INSTR Function to Find a String in a Cell
Steps:
- Press Alt+F11. It will open the Visual Basic Editor.
- Click on Insert > Module.
- Enter the following code in the editor:
Sub instr_function()
Dim cell As Range
Dim search_range As Range
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
Exit Sub
End If
For Each cell In search_range
If InStr(1, cell.Value, "Mr.") > 0 Then
cell.Offset(0, 1).Value = "Male"
Else
cell.Offset(0, 1).Value = "Female"
End If
Next cell
MsgBox "Done"
End Sub- Save it and click on F5 to run the VBA code.
VBA Breakdown
- Create our Sub-procedure as:
Sub instr_function()- Declare variables as:
Dim cell As Range
Dim search_range As Range- Take ranges of cells from the user input as:
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
Exit Sub
End If- Apply the For Loop as:
For Each cell In search_range
If InStr(1, cell.Value, "Mr.") > 0 Then
cell.Offset(0, 1).Value = "Male"
Else
cell.Offset(0, 1).Value = "Female"
End If
Next cell- InStr(1, cell.Value, “Mr.”) > 0 : If InStr has its location, it will return more than zero.
- Offset(0, 1).Value = “Male”: If found, set the value of the adjacent cell to “Male”.
- Finally, end the macro as
End Sub- Select the range of cells B5:B10.
- Click on OK.
You will get the results here.
As you can see, we have successfully found the words “Mr.” and “Ms.” and set their genders to the adjacent cells.
Read More: How to Find String with VBA in Excel
Method 2: Applying a VBA Code with the FIND Method to Find a String in a Cell
Steps:
- Press Alt+F11. It will open the Visual Basic Editor.
- Click on Insert > Module.
- Enter the following code in the editor:
Sub find_method()
Dim cell As Range
Dim search_range As Range
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
Exit Sub
End If
For Each cell In search_range
Set cell = cell.Find("Mr.")
If Not cell Is Nothing Then
cell.Offset(0, 1).Value = "Male"
End If
Next cell
For Each cell In search_range
Set cell = cell.Find("Ms.")
If Not cell Is Nothing Then
cell.Offset(0, 1).Value = "Female"
End If
Next cell
MsgBox "Done"
End Sub- Save it and click on F5 to run the VBA code.
VBA Breakdown
- Create our Sub-procedure as:
Sub find_method()- Declare variables as:
Dim cell As Range
Dim search_range As Range- Take ranges of cells from the user input as:
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
Exit Sub
End If- Apply the first For Loop as:
For Each cell In search_range
Set cell = cell.Find("Mr.")
If Not cell Is Nothing Then
cell.Offset(0, 1).Value = "Male"
End If
Next cell- Offset(0, 1).Value = “Male“: If found, the adjacent cell value will be set to “Male.”
- Apply the second For Loop as:
For Each cell In search_range
Set cell = cell.Find("Ms.")
If Not cell Is Nothing Then
cell.Offset(0, 1).Value = "Female"
End If
Next cell- Offset(0, 1).Value = “Female“” : If found, it will set the adjacent cell value to “female.”
- End the macro as:
End Sub- Select the range of cells B5:B10.
- Click on OK.
You will get the results here.
We have successfully found the words “Mr.” and “Ms.” and set their genders to the adjacent cells.
Things to Remember
- The InStr function returns the string’s starting position. If it doesn’t find one, it returns 0.
- The Find method returns the range object if it finds the string.
- The InStr function is much more effective at searching any string from a cell. However, it may slow down if there is a large amount of data.
Download the Practice Workbook
Download this practice workbook.
