Function Problems

Excel Formula to Check if a Cell Contains Specific Text

Bushra Parveen Sep 25, 2026 869 views

Question details

Check whether a cell in column A contains a specific word (like 'accounts'). If the text is present, column B should remain blank; otherwise, column B should repeat the original value from column A.

Excel Formula to Check Whether a Cell Contains Specific Text

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Conditionally extracting or hiding cell data based on whether a specific text string is found within the source cell.
Observed behavior: Need a proper formula to automate the text search and return conditional values without manually filtering or deleting the data.

Before you start

Ensure your regional settings are verified, as some regions require semicolons (;) instead of commas (,) to separate formula arguments.

Solution 1Recommended

Use IF, ISNUMBER, and SEARCH Functions

Combining these three standard functions provides the most reliable way to perform partial text matches within a cell while ignoring case sensitivity.

The SEARCH function looks for the target text and returns its starting position as a number. If the text isn't found, it returns an error. The ISNUMBER function checks if the result is a number (meaning the text was found), and the IF function handles the final conditional output.

Select the target cell

Click on cell B1 (or the corresponding cell in your target column where you want the result to appear).

Enter the formula

Type the formula: =IF(ISNUMBER(SEARCH("accounts",A1)),"",A1) into the formula bar and press Enter.

Apply to the entire column

Click on cell B1 again, hover over the small green square at the bottom-right corner (the fill handle), and double-click or drag it down to apply the formula to the rest of the rows.

Case Sensitivity and Syntax: The SEARCH function is not case-sensitive. If you need a strict case-sensitive match, replace SEARCH with the FIND function. Additionally, if the formula returns an error, try replacing the commas with semicolons: =IF(ISNUMBER(SEARCH("accounts";A1));"";A1).

Efficient Data Processing with WPS

Easily Manage Complex Formulas in WPS Spreadsheet

WPS Spreadsheet provides a seamless environment for utilizing complex functions like IF, ISNUMBER, and SEARCH. It is designed to help you process text, analyze data, and apply conditions efficiently.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your text data.
2. Insert the formula: Click on the cell in column B and type '=' to trigger the formula prompt.
3. Configure the search parameters: Input =IF(ISNUMBER(SEARCH("accounts",A1)),"",A1) into the cell.
4. Fill down the column: Press Enter, then double-click the fill handle at the bottom-right of the cell to populate your conditional data.

100% compatible with Microsoft Excel formulas and file formats (.xlsx).Intuitive formula autocomplete to reduce syntax typing errors.Lightweight design ensures fast calculation for massive datasets.Completely free to download and use for your daily office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula show a syntax error?

This usually happens due to Windows regional settings. If your computer uses a comma as a decimal point, you must use a semicolon (;) to separate function arguments. Change the formula to: =IF(ISNUMBER(SEARCH("accounts";A1));"";A1).

How can I make the text search case-sensitive?

If you want to distinguish between uppercase and lowercase letters (e.g., 'Accounts' vs 'accounts'), use the FIND function instead of SEARCH. The formula becomes: =IF(ISNUMBER(FIND("Accounts",A1)),"",A1).

Can I check if a cell contains one of multiple specific words?

Yes, but you will need an array formula or a combination with SUMPRODUCT. For example, to check for 'accounts' or 'finance', you can use =IF(SUM(COUNTIF(A1,{"*accounts*","*finance*"})),"",A1).

Excel & Data Problems