How To Find String With VBA In Excel (8 Examples) - ExcelDemy

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.

• Enter the following code inside a VBA Sub Procedure:

Sub INSTR_Example() MsgBox InStr("Happiness is a choice", "choice") End Sub

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

• 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

• 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 second choice 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

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

it gives us the position (16) of the first occurrence of the text choice.

• However, if we use InStrRev,

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

• 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

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

• 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

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:

VBA to Find Strings.xlsm

Related Articles

• How to Find Substring Using VBA in Excel
• VBA Instr Case Insensitive

Get FREE Advanced Excel Exercises with Solutions!