How to Find String in a Cell Using VBA in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview Image


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

Syntax of InStr Function

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:

Sample Data Set to Find String in Cell Using VBA in Excel

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.

Opening VBA Module to Find String in Cell

  • 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.

Using VBA Code with INSTR Function to Find String in Cell in Excel

VBA Breakdown
  • 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.

Selecting Ranges

  • 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.

Showing Results by Using VBA Code with INSTR Function to Find String in Cell in Excel

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.

Opening VBA Module to Find String in Cell

  • 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.

Applying VBA Code with FIND Method to Find String in Cell in Excel

VBA Breakdown
  • 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.

Selecting Ranges

  • 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.

Showing Results by Applying VBA Code with FIND Method to Find String in Cell in Excel


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo