How to Replace a Cell with One Word When It Contains Specific Text in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that checks whether a cell contains specific text such as "xyz" anywhere in its content and then returns the word "abc". If the original cell itself must be replaced, a VBA or Power Query approach is required.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with cells that contain multiple sentences and wants to automatically output a fixed value when a keyword is present.
- Observed behavior
- A worksheet formula cannot overwrite its own source cell, so the result must be placed in a separate cell unless VBA or Power Query is used.
Before writing the formula, identify the cell you want to check, such as A2, and the exact keyword to search for. If the text may contain extra spaces, consider using TRIM around the cell reference to avoid unexpected mismatches.
Use the IF, ISNUMBER, and SEARCH formula in a separate cell
Best when you only need the returned word in another cell and want to keep the original text unchanged.
The formula uses SEARCH to find the keyword anywhere in the cell. ISNUMBER converts the search result to TRUE or FALSE, and IF returns "abc" when the keyword exists; otherwise it returns the original cell value.
Click an empty cell where you want the result to appear, such as B2.
Type =IF(ISNUMBER(SEARCH("xyz",A2)),"abc",A2) and adjust A2 to the cell you are checking.
Press Enter, then drag the fill handle down or across to copy the formula to other cells as needed.
Replace the original cell content with VBA or Power Query
Use this when the result must overwrite the source cell instead of appearing in a separate column.
Find Specific Text and Return a Value with WPS Spreadsheets
Use the IF, ISNUMBER, and SEARCH formula in WPS Spreadsheets to check a cell for specific text and return one word when found. It works across long text strings and keeps your original cell values intact without VBA or Power Query.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the file that contains the text cells you want to check.
- 2. Select the result cell: Click an empty cell where you want the returned word to appear, such as B2.
- 3. Enter the detection formula: Type =IF(ISNUMBER(SEARCH("xyz",A2)),"abc",A2) and replace the keyword, return value, and cell reference as needed.
- 4. Apply the formula and review: Press Enter, then drag the fill handle to copy the formula down or across the relevant cells.
Frequently Asked Questions
Can the formula replace the text in the same cell?
No. A formula cannot overwrite its own source cell. Place the result in another cell, or use VBA or Power Query for in-place replacement.
Does SEARCH distinguish between uppercase and lowercase?
No. SEARCH is case-insensitive, so it finds "xyz", "XYZ", or mixed-case versions. If you need case-sensitive matching, use FIND instead of SEARCH.
What happens if the keyword is not found?
When the keyword is absent, the formula returns A2, the original cell value. Replace A2 with an empty string or another value in the formula if you want a different result.
Can I search for text anywhere in multiple sentences?
Yes. SEARCH checks the entire cell text, so it can find the keyword even when the cell contains multiple sentences.
How do I apply this formula to an entire column?
Enter the formula in the first row next to your data, then drag the fill handle down. You can also double-click the fill handle to fill the formula through adjacent rows.
