Working with strings in Microsoft Excel is a common scenario. You can find strings from a cell easily by a lot of formulas. Sometimes, it takes a long time to solve these. It may be complex to remember too. But, if you have an idea about Excel’s VBA, you know how useful and time-saving it is. In this tutorial, you will learn how to find a string in the cell using VBA in Excel with suitable examples and proper illustrations. Here is a gif for a better understanding of how we have done this.
Introduction to VBA INSTR Function to Find String in a Cell
In this tutorial, the INSTR function will be one of the main methods to find a string in the cell using VBA. This function is pretty simple and easy to use. You can find any string and its position from any cell.
The VBA InStr function indicates if a text string is detected in another text string. It returns 0 if the text is not found. Otherwise, it returns the character position where the text is located.
Syntax
InStr([start],string1,string2,[compare])
Arguments Explanation
| Arguments | Requirement | Description |
|---|---|---|
| [start] | Optional | The starting position of the search. Enter 1 to start searching from position 1. |
| string | Required | The Main string from where you want to find. |
| substring | Required | The string you want to find from the main string. |
| [compare] | Optional |
Case-sensitivity 0 for case sensitive (vbBinaryComapre) 1 for case insensitive (vbTextCompare) |
2 Easy Ways to Find String in Cell Using VBA in Excel
In the next sections, we will show you two methods to Find a String in a Cell Using VBA in Excel. There will be two examples to demonstrate these methods.
To demonstrate these methods, we are going to use this dataset:
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 VBA Code with INSTR Function to Find String in Cell in Excel
Now, if you know Excel’s VBA then you should try this method. This method is not only efficient but also will save you a lot of time.
Steps:
- Firstly, press Alt+F11 on your keyboard. After that, it will open the Visual Basic Editor.
- Next, click on Insert > Module.
- After that, type 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- Then, save it and click on F5 to run the VBA code.
- First, we will create our Sub-procedure as
Sub instr_function()- Secondly, we will declare variables as
Dim cell As Range
Dim search_range As Range- Thirdly, we will 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- Besides, we 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, will set the value of the adjacent cell to “Male”.
- Finally, end the macro as
End Sub- Besides, select the range of cells B5:B10.
- After that, click on OK.
- Finally, 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 VBA Code with FIND Method to Find String in Cell in Excel
Now, we can also use the Find method to find any string from a cell. We don’t use this method too often. We use this method to find the location of a string from a range of cells. But, we can modify it to search strings from a cell.
Steps:
- Firstly, press Alt+F11 on your keyboard. After that, it will open the Visual Basic Editor.
- Next, click on Insert > Module.
- After that, type 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- Then, save it and click on F5 to run the VBA code.
- First, we will create our Sub-procedure as
Sub find_method()- Secondly, we will declare variables as
Dim cell As Range
Dim search_range As Range- Thirdly, we will 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- Besides, we 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, it will set the adjacent cell value to “Male”
- Then, we 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”
- Finally, end the macro as
End Sub- Besides, select the range of cells B5:B10.
- After that, click on OK.
- Finally, 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.
Things to Remember
- The InStr function returns the starting position of the string. It returns 0 if it doesn’t find one.
- The Find method returns the range object if it finds the string.
- The InStr function is much more effective to search any string from a cell. But, it may slow if there is a large amount of data.
Download Practice Workbook
Download this practice workbook.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to find a string from the cell using VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
