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

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.


Download Practice Workbook

Download this practice workbook


INSTR Function Using VBA 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], string, substring, [compare] )

Arguments

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

 


Simple INSTR Examples to Find String Using VBA

To give you some better understandings, we are providing you with some code snippets.

Text Position Using INSTR

Sub INSTR_Function()
MsgBox InStr("Hello World", "Hello")
End Sub

It will return 1 as INSTR found the “Hello” from position 1.

Sub INSTR_Function()
MsgBox InStr("World Hello", "Hello")
End Sub

It will return 7 as INSTR found the “Hello” from position 7.

Start Position of INSTR

Sub INSTR_Function()
MsgBox InStr(4,"Tom JerryTom", "Tom")
End Sub

Here, we have “Tom”  from position 1. But, it will return 11 as the search started from position 4. Previous values won’t count.

Case Sensitivity 

Sub INSTR_Function()
MsgBox InStr("World Hello", "hello",0)
End Sub

It will return 0. Because this is case-sensitive. It couldn’t find any match.

To make it case-insensitive, change the last argument to 1 or vbTextComapre.

Sub INSTR_Function()
MsgBox InStr("World Hello", "hello",1)
End Sub

Now, it will return 7.

Find String in a Text

Now, we can find any string from a text using the INSTR function. The following code snippet will give you a better idea:

Sub INSTR_Function()
If InStr(“Microsoft Excel”, “Excel”) > 0 Then
  MsgBox “String Found”
Else
  MsgBox “Text Not Found”
End If
End Sub

It will show “String Found” as Excel is present in Microsoft Excel.

Read more: How to Find String with VBA in Excel


2 Ways to Find String in Cell Using VBA in Excel

In the next sections, we will show you two methods to Find String in 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:

dataset for excel vba find string in cell

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

1. INSTR Function  Find String in Cell using VBA in Excel

To build the code, press Alt+F11 to open the VBA Editor. Then click on Insert > Module.

insert vba module

Building the Code

📌Step 1: Creating Sub-procedure to Write Codes

Sub instr_function()
End Sub

Here, we will write our code.

📌 Step 2: Declare Variables

Sub instr_function()

Dim cell As Range
Dim search_range As Range

End Sub

📌 Step 3: Taking Range of Cells from the  User as Input

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

End Sub

We will take the range of cells from the user input and store it into the search_range variable.

Type:=8 means cell.

Application.DisplayAlerts = False”  will disable all the alerts if the user does not give any user input.

The If segment will check whether the user presses the cancel button or not.

📌 Step 4: Perform InStr Operations in a Loop

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 

End Sub

InStr(1, cell.Value, “Mr.”) > 0 : If InStr has its location, it will return more than zero.

cell.Offset(0, 1).Value = “Male”: If found, will set the value of the adjacent cell to “Male

📌 Step 5: Show Final Message after Completing

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

Running the Code

📌 Steps

 First, press Alt+F8 on your keyboard to do the Macro dialog box.

Then, select instr_function

Click on Run.

macro dialog box for excel vba find string

Select the range of cells B5:B10

select range of cells to find in excel

After that, click on OK.

exccel vba find string in cell result

As you can see, we have successfully found the words “Mr.” and “Ms.” and set their genders to the adjacent cells.


Similar Readings:


2. 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 string from a cell.

Syntax:

expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression: This is a variable that represents a Range object.

What: Only this field is required. This represents the value you want to find.

The rest of the parameters are optional.

Find method returns a Range object that represents the first cell where that information is found.

It returns Nothing if there is no match.

Now, let’s build and run the code.

To build the code, press Alt+F11 to open the VBA Editor. Then click on Insert > Module.

insert vba module

Building the Code

📌 Step 1: Sub-procedure

Sub find_method()
End Sub

Between this procedure, we will write our whole code.

📌 Step 2: Declaring Variables to Store Input

Sub find_method()

Dim cell As Range
Dim search_range As Range

End Sub

📌 Step 3: User Input of Search Range

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

End Sub

We will take the range of cells from the user input and store it into the search_range variable.

Type:=8” means cell.

Application.DisplayAlerts = False”  will disable all the alerts if the user does not give any user input.

The If segment will check whether the user presses the cancel button or not.

📌 Step 4: Create First Loop to Find “Mr.” 

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

End Sub

The first loop will search for the word “Mr.”.

 “ cell.Offset(0, 1).Value = “Male: If found, it will set the adjacent cell value to “Male

📌 Step 5: Create Second Loop to Find “Ms.” 

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

End Sub

The first loop will search for the word “Ms.”.

 “ cell.Offset(0, 1).Value = “Female“” : If found, it will set the adjacent cell value to “female

📌 Step 6: Show Final Message after Completing

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

After completing, it will show a message “Done

Running the Code

📌 Steps

 First, press Alt+F8 on your keyboard to do the Macro dialog box.

Then, select find_method

Click on Run.

macro dialog box to run the VBA

Select the range of cells B5:B10

select range of cells to find string

After that, click on OK.

final output of excel vba find string

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 effective to search any string from a cell. But, it may slow if there is a large amount of data.


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

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo