How to Find a String with VBA in Excel (8 Examples)

Introduction to InStr Function in Excel VBA

The InStr function in Excel VBA allows you to find the position of specific strings within a given text.

Generic Syntax:

InStr([start], string1, string2, [compare])

Where,

Arguments Required/ Optional Definition
start Optional Starting position of the search.

  • By default, the InStr function calculates the character position by counting from 1, not from the start position. So, you can leave this blank if you want to.
string1 Required The string to search in, Primary String.
string2 Required The string to search for in the Primary String.
compare Optional The InStr function is case-sensitive by default. But if you want to run a case insensitive InStr, then you can pass the argument here to perform a certain comparison. This argument can be the following values,

  • vbBinaryCompare -> performs a binary comparison, return value 0
  • vbTextCompare -> performs a text comparison, return value 1
  • vbDatabaseCompare -> performs a database comparison, return value 2

By default, InStr takes vbBinaryCompare as the compare argument.


Example 1: Find Position of Text in a String

  • Open the Visual Basic Editor by pressing Alt + F11.

  • Insert a new module.

  • Write the following code inside a VBA Sub Procedure:
Sub INSTR_Example()
MsgBox InStr("Happiness is a choice", "choice")
End Sub

vba find text in string

  • Run the code (F5 or Run Sub/UserForm).

run vba to find text in string

  • The message box will display the position of the word choice (which is 16 in this case).

Explanation:

Our primary string, “Happiness is a choice” is a 21-letter sentence (with spaces) and we wanted to find the position of the text “choice” in that string. The text “choice” started from the 16th position of the primary string; hence we got number 16 as our output in the message box.


Example 2: Find Text from a Specific Position in a String

  • Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Modify the code as follows:
Sub INSTR_Example()
MsgBox InStr(17, "Happiness is a choice", "choice")
End Sub

vba to find text from specific position in string

  • Run the code.

Running this code will give you the position of the second occurrence of choice (which is 27).

Explanation:

As we already know (from the phase 1 discussion) that the text “choice” started from the position of 16, so we inserted two “choice” in the primary string and set 17 as our 1st parameter to skip the first “choice”. So, we Run the above macro and it showed us position number 27 which is exactly the position number of the secondchoice” in the given string.

Read More: How to Find String in a Cell Using VBA in Excel


Example 3: Case-insensitive Search

We wanted to find the position of the word “Choice” with a capital “C” in the string “Happiness is a choice” where choice is written with a small “c”.

  • By default, InStr is case-sensitive.
  • To make it case-insensitive, use the vbTextCompare argument:
Sub INSTR_Example()
MsgBox InStr(1, "Happiness is a choice and choice", "Choice", vbTextCompare)
End Sub

vba to find case insensitive text in string

  • Run the code.

Now it will find Choice regardless of capitalization.

You will get the position of the text from the string, whether the text is written in capital letters or small letters.


Example 4: Find Text from the Right of a String

Until now, the InStr function only provided the position from the left side of a string. But what if you need to find the text position from the right side? Enter the InStrRev function, which searches from the right. It works similarly to InStr but finds the position of a text from the right side of the string. Let’s compare the following examples:

  • If we run the following code with the InStr function then,

vba to find text from left in string

it gives us the position (16) of the first occurence of the text “choice”.

  • However, if we use InStrRev,

vba to find text from right in string

it gives us the position (27) of the last occurrence of “choice“.


Example 5: Find the Position of a Character in String

You can also find the position of a specific character in a string. For instance, consider the following VBA code snippet:

Sub Find_Character()

Dim z As Long
z = InStr("Happiness is a choice", "e")
MsgBox z
End Sub

VBA to Find Position of a Character in String

  • When you run this macro, it will return the position of the first “e” in the given string (which is at position 7).


Example 6: Find a Substring in a String

To determine whether a string contains a specific substring, you can use an IF Statement. Here’s an example:

Public Sub FindSub()
If InStr("Happiness is a choice", "choice") = 0 Then
MsgBox "No match found"
Else
MsgBox "Match found"
End If
End Sub

VBA to Find Substring in a String

Running this macro will display Match found because the primary string Happiness is a choice contains the word choice.


Example 7: Find a String in a Cell Range

Suppose you want to search for a certain text in a cell range and return a specific string. Consider the following code:

Sub Find_String_in_Range()
Dim cell As Range
For Each cell In Range("B5:B10")
If InStr(cell.Value, "Dr.") > 0 Then
cell.Offset(0, 1).Value = "Doctor"
End If
Next cell
End Sub

VBA to Find String in a Cell Range

  • Run the code and the result is shown below.

Running this code will find instances of Dr. in the specified cell range and replace them with Doctor.


Example 8: Find String in a Cell

If you want to search for a specific text in a single cell and return a certain string, use the following code:

Sub Find_String_in_Cell()
If InStr(Range("B5").Value, "Dr.") > 0 Then
Range("C5").Value = "Doctor"
End If
End Sub

VBA to Find String in a Cell

This macro searches for “Dr.” in Cell B5 and, if found, replaces it with “Doctor” in Cell C5. You can adapt the macro for other search terms and replacements as needed. For example, to find “Prof.” and replace it with “Professor,” adjust the values accordingly.


Download Practice Workbook

You can download the practice workbook from here:


Related Articles:

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo