Function Problems

How to Use Excel Formulas to Find Partial Text Matches

Olivia Miller Sep 28, 2026 874 views

Question details

The user wants to create an Excel formula that searches for a specific text string or category within a cell and returns 'YES' if a partial match is found.

How to Use Excel Formulas for Partial Text Matching

Product: Excel
Device & OS: not provided
Scenario: Searching for specific sub-strings (like 'JO' inside 'Jones') across different cells to categorize, extract, or flag specific data.
Observed behavior: The user attempted to use asterisk wildcards for partial text searches, but the formula did not evaluate correctly and failed to return the expected matches.

Before you start

Ensure your data is organized in clear columns without merged cells, and identify the exact text string or cell reference you want to use as your search criteria.

Solution 1Recommended

Using IF and COUNTIF with Wildcards for Partial Matches

This method uses the COUNTIF function combined with asterisk wildcards to detect partial text and return a 'YES' if found.

The COUNTIF function natively supports wildcard characters, making it perfect for partial text matching. By placing asterisks (*) around your search term, Excel looks for that sequence of characters anywhere in the target cell.

Select the output cell

Click on the cell where you want the 'YES' or 'NO' result to appear (for example, cell B1).

Input the IF and COUNTIF formula

Type the formula `=IF(COUNTIF(A1, "*JO*"), "YES", "NO")`. This tells the spreadsheet to check if the text 'JO' appears anywhere in cell A1.

Use cell references for dynamic searches

To use another cell as the search criteria instead of typing 'JO' directly, use ampersands to concatenate the wildcards. Type `=IF(COUNTIF(A1, "*"&C1&"*"), "YES", "NO")` where C1 contains your search text.

Apply to the entire column

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

Using IF and COUNTIF with Wildcards for Partial Matches

Wildcard Tip: The asterisk (*) represents any number of characters. If you need to match exactly one unknown character, use a question mark (?) instead.

Solution 2

Using ISNUMBER and SEARCH Functions

An alternative approach that does not rely on wildcard characters, ideal for searching substrings within complex datasets where wildcards might conflict.

WPS Spreadsheet Solution

Easily Find Partial Text Matches with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas and wildcard searches, making it incredibly simple to find and categorize partial text matches in your datasets without requiring complex workarounds.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the text data you need to search.
2. Enter your partial match formula: Click your target cell and input `=IF(COUNTIF(A1, "*"&C1&"*"), "YES", "NO")` to check for partial matches using another cell as a reference.
3. Drag to fill: Use the fill handle at the bottom right of the active cell to drag the formula down your column and process all your data instantly.

Fully compatible with Microsoft Excel formulas like IF, COUNTIF, and SEARCH.Advanced data filtering and native wildcard support for precise partial text matching.Free and lightweight alternative to heavy spreadsheet applications.Familiar tabbed interface for a seamless transition and zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my wildcard formula not working in Excel?

If your wildcard formula isn't working, ensure you are using functions that natively support wildcards (like COUNTIF, SUMIF, or VLOOKUP). The IF function alone does not process wildcards. Also, verify that your wildcards are enclosed in quotation marks, such as "*text*".

How do I search for a partial text match using a cell reference instead of text?

To use a cell reference with wildcards, you must concatenate the wildcards and the cell reference using the ampersand (&) symbol. For example, instead of typing "*JO*", you would type "*"&B1&"*" where B1 contains the letters JO.

Is the SEARCH function case-sensitive?

No, the SEARCH function is not case-sensitive, meaning 'JO' and 'jo' will both be found. If you require a case-sensitive partial match, you must use the FIND function instead.

Can I return a value other than YES or NO?

Yes, you can easily customize the output of the IF function. Simply replace "YES" and "NO" in your formula with your desired text (enclosed in quotes), cell references, or even mathematical calculations.

Excel & Data Problems