Function Problems

How to Find Exact Substrings in Excel Part Numbers

Natalie Taylor Oct 1, 2026 868 views

Question details

The user needs an Excel formula to find exact space-separated words (like NPTF) in a cell without falsely matching partial text inside longer words (like NPTFS).

How to Use an Excel Formula to Find Exact Substrings

Product: Excel
Device & OS: not provided
Scenario: Searching for specific part numbers or exact substrings within a cell containing multiple space-separated words.
Observed behavior: Standard search functions often return false positives by finding partial matches inside longer words instead of evaluating exact whole words.

Before you start

Ensure your target words (e.g., NPTF, NPS, UNF) are listed in a separate reference range (like F2:F4) and that you are using a version of Excel or WPS Spreadsheet that supports modern dynamic array functions like TEXTSPLIT.

Solution 1Recommended

Use TEXTSPLIT and XMATCH for Row-by-Row Matching

This method splits the cell contents into individual words and checks for exact matches against a predefined list.

By splitting the text using a space delimiter, you guarantee that only whole words are evaluated, preventing partial matches from triggering a false positive.

Set up your target list

Enter the exact substrings you want to find (e.g., NPTF, NPS, UNF) into a range of cells, such as F2:F4.

Enter the formula

In cell B2, input the following formula: =OR(ISNUMBER(XMATCH(TEXTSPLIT(A2," "),$F$2:$F$4)))

Apply to other rows

Press the Enter key, then click and drag the fill handle at the bottom-right corner of cell B2 down to apply the formula to the rest of your dataset.

Use TEXTSPLIT and XMATCH for Row-by-Row Matching

Formula Breakdown: TEXTSPLIT separates the words, XMATCH checks for exact matches in the F2:F4 array, ISNUMBER confirms a match was found, and OR returns TRUE if any word matches.

Solution 2

Use BYROW for an Automatic Spill Result

Ideal for processing an entire column at once without needing to drag the fill handle down manually.

Advanced Data Processing

Use WPS Spreadsheet to Handle Complex Array Formulas

WPS Spreadsheet fully supports advanced dynamic array formulas, making it incredibly easy to extract, match, and manipulate text strings in your datasets.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the part numbers.
2. Input your reference data: Type your exact target substrings in a separate column range to serve as your lookup array.
3. Apply the array formula: Paste the provided XMATCH and TEXTSPLIT formula into your target cell to instantly identify exact word matches.

Supports modern dynamic array functions like TEXTSPLIT and XMATCH for seamless data analysis.Highly compatible with Microsoft Excel formulas and file formats (.xlsx).Free, lightweight, and features a familiar user interface for an easy transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when using the BYROW formula?

A #SPILL! error occurs when the dynamic array formula tries to output results into multiple cells, but one or more of those destination cells already contain data. To fix it, simply clear the obstruction in the cells below the formula so it has enough space to spill the results.

Will this formula match case-sensitive substrings?

By default, the XMATCH function is not case-sensitive. If you need a strict case-sensitive match, you would need to incorporate the EXACT function into your formula logic instead of relying solely on XMATCH.

Can I use a different delimiter besides a space?

Yes. In the TEXTSPLIT(A2," ") portion of the formula, you can replace the space character with any other delimiter your data uses, such as a comma (",") or a hyphen ("-").

Excel & Data Problems