Excel VBA Find Partial Text: Methods, Code, and Tips

To find cells containing part of a text string in Excel VBA, use the range’s Find method with LookAt:=xlPart. For example, searching for north this way can match both “Northwind” and “north region”; use MatchCase:=False if capitalization should not matter.

How VBA Finds Partial Text

Excel’s VBA Range.Find method searches a specified range and returns the first cell that matches, or Nothing if there is no match. The key setting is LookAt: xlPart allows a match within a cell’s contents, while xlWhole requires the entire cell contents to match. If you omit LookAt, you risk using a setting left over from an earlier search.

This example searches A2:A100 on a worksheet named Sheet1 and reports the first matching cell. Change the worksheet, range, and search text to suit your workbook.

Sub FindPartialText()
    Dim searchRange As Range
    Dim foundCell As Range

    ' Set the cells to search on the worksheet containing this workbook.
    Set searchRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")

    ' Search for the text anywhere in a cell's evaluated value.
    Set foundCell = searchRange.Find( _
        What:="north", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No partial match was found."
    Else
        MsgBox "Found " & foundCell.Address(False, False) & ": " & foundCell.Value
    End If
End Sub

The code searches values rather than formula text, searches by rows, and ignores capitalization. Setting After to the last cell makes the search begin at the start of the range as it wraps around. If you run it, save a copy of the workbook first: macros cannot be undone with Ctrl+Z.

Choose the Right Search Method

Find is a good default when you want Excel to locate a matching cell in a range, especially when the range is large or you need to use Excel’s search options. It gives you a Range object, so the macro can inspect the address, value, or other properties of the matched cell.

For a straightforward check of each cell’s text, VBA’s InStr function is another option. It returns the position where one string occurs inside another, or zero when it does not occur. This example checks cells in A2:A100 and prints matches to the Immediate window in the VBA editor:

Sub CheckWithInStr()
    Dim searchRange As Range
    Dim cell As Range

    Set searchRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")

    ' Compare without regard to capitalization; skip error-valued cells.
    For Each cell In searchRange.Cells
        If Not IsError(cell.Value) Then
            If InStr(1, CStr(cell.Value), "north", vbTextCompare) > 0 Then
                Debug.Print cell.Address(False, False), cell.Value
            End If
        End If
    Next cell
End Sub

Use vbTextCompare for a case-insensitive comparison, or vbBinaryCompare for a case-sensitive one. Unlike Find, InStr does not interpret asterisks and question marks as wildcards. It can be easier to customize when you need to apply additional logic to every cell, but it checks cells one by one.

Find Every Matching Cell

A single call to Find returns one match, not a collection of all matches. To process every matching cell, save the address of the first match, then use FindNext until the search returns to that address. The address check matters because Excel’s search wraps around to the beginning of the range.

Sub FindAllPartialMatches()
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set searchRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")

    With searchRange
        Set foundCell = .Find( _
            What:="north", _
            After:=.Cells(.Cells.Count), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)

        If Not foundCell Is Nothing Then
            firstAddress = foundCell.Address
            Do
                Debug.Print foundCell.Address(False, False), foundCell.Value
                Set foundCell = .FindNext(foundCell)
                If foundCell Is Nothing Then Exit Do
            Loop While foundCell.Address <> firstAddress
        End If
    End With
End Sub

This prints one line for each matching cell in the Immediate window; it does not print each occurrence of the search text inside a single cell. For example, a cell containing “north, north-east” is still one matching cell. If you want to collect matches for a report, replace Debug.Print with code that writes each address or value to a results range. Keep the first-address check so the loop stops after one complete pass.

Search Values or Formula Text

The LookIn argument determines what Excel searches. Use LookIn:=xlValues to search a cell’s evaluated value, such as the displayed text produced by a formula. Use LookIn:=xlFormulas when you need to search the formula itself. For example, a cell displaying “Northwind” because of a formula may match a search for “North” with xlValues; searching with xlFormulas instead examines the formula’s text.

This distinction can explain why a search appears to miss a cell. A formula might return a partial-match word even though that word does not appear in the formula, or the formula might contain a word that is not part of its displayed result. Choose the search target based on what you are trying to find: the result shown to users or the calculation written in the cell.

Wildcards, Case, and Special Characters

With Find, an asterisk (*) represents any sequence of characters, and a question mark (?) represents one character. For example, What:="north*" can match text beginning with “north,” while What:="n?rth" can match “north” or another five-character string with a character in the question mark’s position. Wildcards are useful when you do not know every character in the text.

To search for an actual wildcard character rather than use it as a pattern, prefix it with a tilde: use ~* to search for an asterisk, ~? for a question mark, and ~~ for a tilde. The InStr method does not use these wildcard rules; it searches for the literal string you pass to it.

Set MatchCase:=True when capitalization must match, or MatchCase:=False when it should not. Be explicit about this setting, as well as the other important Find arguments. The Find dialog’s options are shared with VBA searches and can persist from one search to another. Passing the settings in your code makes the macro’s intended behavior clearer and more predictable.

Common Problems and How to Fix Them

No match even though the text seems present: Check that you are searching the intended worksheet and range, and choose xlValues or xlFormulas according to what should match. Also check whether the cell contains an error value or unexpected spaces.
The search finds only an exact match: Confirm that the code uses LookAt:=xlPart, not xlWhole. These settings have different meanings.
The macro errors when no match exists: A failed search returns Nothing. Check If foundCell Is Nothing Then before asking for the cell’s address or value.
The macro finds the wrong match: Make the range, case setting, search direction, and search order explicit. Also check whether wildcard characters in the search text are being treated as patterns.
The macro loops indefinitely while finding all matches: Stop when the returned address equals the first match’s address. A search can wrap around rather than ending automatically at the last cell.

A related misconception is that LookAt:=xlPart searches for a word regardless of where it appears. It does allow the text to occur anywhere in a cell, but it does not identify word boundaries. Searching for “art” can match “carton,” for example. If you need whole words only, you will need additional logic to check the characters around each occurrence.

When to Use Excel’s Find Dialog Instead

For a one-time manual search, you may not need VBA. In Excel for Microsoft 365 on Windows, press Ctrl+F to open Find, enter the text, and use Find All or Find Next. Open Options to choose whether to search within formulas or values, match case, and search the whole cell. The dialog’s “Match entire cell contents” option corresponds to an exact whole-cell search, not a partial search. On Mac, use Command+F to open Find; available options may be arranged differently from Windows.

Use VBA when the search is part of a repeatable task, when you need to act on matches, or when you want the same range and settings applied consistently. For example, a macro can locate a partial product name and then read a value from the same row. If you need all matching records or a dynamic list rather than a macro action, worksheet functions such as SEARCH or ISNUMBER may also be appropriate; they work in formulas and have different uses from VBA’s Range.Find.

Running and Adapting the Macro

Put the procedure in a standard VBA module in the workbook that contains the data. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code. You can also use the Visual Basic button on the Developer tab. Change "Sheet1" to the worksheet’s tab name and adjust the range so it covers the data you want to search. If the macro is stored in a different workbook, check whether ThisWorkbook points to the workbook containing the macro rather than the workbook containing the data.

Save a macro-enabled workbook as .xlsm if you need to keep the VBA code. Excel for the web can open workbooks containing macros, but it cannot create, edit, or run VBA macros; use the desktop Excel application for those tasks. On Mac, Excel also supports VBA, though editor shortcuts and some interface details can differ with keyboard settings. Keep a backup before running code that changes worksheet contents, since VBA changes cannot be reversed with Ctrl+Z.

Frequently Asked Questions

How do I find part of a cell value in Excel VBA?
Call Range.Find on the cells to search and set LookAt:=xlPart. Check whether the returned object is Nothing before using it.

What is the difference between xlPart and xlWhole?
xlPart matches the search text anywhere within a cell’s contents. xlWhole matches only when the entire cell contents equal the search text.

How do I make VBA Find ignore uppercase and lowercase?
Pass MatchCase:=False to Find. For InStr, use the comparison argument vbTextCompare.

Can VBA Find return every partial match?
One call returns the first match. Use FindNext in a loop and stop when the search returns to the first match’s address.

Why does VBA Find return Nothing?
The specified range may contain no match, the wrong sheet or range may be selected, or the search may be looking at formula text when you intended to search values. Verify the range and set all relevant Find arguments explicitly.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.