[VBA Beginners] How to Check if a String Contains Specific Text (Using InStr)

2025年12月26日

Introduction

In Excel VBA, a very common task is checking whether a specific word or phrase exists within a string. For example, you might want to execute a process only if a cell contains the word “Urgent”.

In this article, I will introduce the basic usage of the InStr() function, which is essential for this type of conditional branching.

What is the InStr Function?

The InStr function determines whether a specified string contains another string.

Syntax

InStr(String1, String2)

It returns the position (as a number) where String2 appears within String1.
If the string is found, it returns a value of 1 or greater.
If the string is not found, it returns 0.

Usage Example: Checking for a Specific Phrase

The following example checks if the word “Wood” is contained within the string “Norwegian Wood”.

Sub CheckIfTextExists()
    Dim fullText As String
    Dim searchText As String

    fullText = "Norwegian Wood"  ' Target string
    searchText = "Wood"          ' Search string

    ' Check if 'searchText' is contained inside 'fullText'
    If InStr(fullText, searchText) > 0 Then
        MsgBox "It is contained.", vbInformation
    Else
        MsgBox "It is not contained. Please check.", vbExclamation
    End If
End Sub

Results and Process Flow

InStr("Norwegian Wood", "Wood") returns 11 because “Wood” starts at the 11th character.
Therefore, the condition InStr(...) > 0 becomes True.
The message “It is contained.” is displayed.

Common Mistake: Pay Attention to Argument Order

In the InStr function, the first argument is the “Target String” (to be searched), and the second argument is the “Search String” (what you are looking for).

If you reverse them, you will not get the correct result:

If InStr(searchText, fullText) > 0 Then ' <-- This is the WRONG order

Correct Order: (Target, Search Pattern)

If InStr(fullText, searchText) > 0 Then

Application: Changing Cell Color if Text is Found

You can use this logic to change the font color of a cell to red if it contains specific words, such as “Error”.

Sub ChangeFontColorIfFound()
    Dim targetCell As Range
    Set targetCell = Range("A1")

    ' Check if the cell value contains "Error"
    If InStr(targetCell.Value, "Error") > 0 Then
        targetCell.Font.Color = RGB(255, 0, 0) ' Change font to Red
    End If
End Sub

Summary

By using the InStr() function, you can easily check if specific text is included in a string. When combined with conditional branching (If statements), you can achieve: