Check Whether Text in One WPS Spreadsheet Cell Appears in Another
Question details
Determine whether the text in a cell in column B appears anywhere within the corresponding cell in column A.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to compare text in matching rows and identify whether the value in column B is contained in the value in column A.
- Observed behavior
- The desired result is TRUE or FALSE, or a custom YES or NO result, based on whether the text is found.
Confirm that the text to search is in column B and the text to be checked is in column A for the same row. Decide whether uppercase and lowercase letters should be treated as different characters before choosing FIND or SEARCH.
Use SEARCH for a case-insensitive text match
Use this method when letter case should not affect the result, such as matching "apple" with "Apple Pie".
SEARCH returns the starting position when the text in B2 is found within A2. ISNUMBER converts that position into TRUE; if no match is found, it returns FALSE.
Click the cell where you want the result to appear, such as C2.
Type =ISNUMBER(SEARCH(B2,A2)) in C2, then press Enter. The formula returns TRUE when B2 appears anywhere in A2 and FALSE when it does not.
Select C2 and drag the fill handle down to apply the formula to the remaining rows.
To display words instead of logical values, enter =IF(ISNUMBER(SEARCH(B2,A2)),"YES","NO") in the result cell.
Use FIND for a case-sensitive text match
Use this method when uppercase and lowercase letters must match exactly.
Find Text Within Another Cell Using WPS Spreadsheet
WPS Spreadsheet lets you compare text across cells with familiar Excel-compatible functions. Use SEARCH for case-insensitive matching or FIND for case-sensitive matching, then fill the formula down to evaluate an entire column.
- 1. Open the worksheet: Open your workbook in WPS Office and select WPS Spreadsheet.
- 2. Choose a result column: Click the first empty cell beside your data, such as C2.
- 3. Add a text-match formula: Enter =ISNUMBER(SEARCH(B2,A2)) for a case-insensitive match, then press Enter.
- 4. Apply the formula to all rows: Drag the fill handle in the lower-right corner of the formula cell down the result column.
Frequently Asked Questions
What is the difference between SEARCH and FIND in WPS Spreadsheet?
Both functions look for text within another text value. Use SEARCH when case does not matter, and use FIND when uppercase and lowercase letters must match exactly.
How do I show YES or NO instead of TRUE or FALSE?
In the result cell, enter =IF(ISNUMBER(SEARCH(B2,A2)),"YES","NO"). Replace B2 and A2 with the cells used in your worksheet.
Why does the formula return FALSE when the text looks the same?
Check for extra spaces, hidden characters, or different letter case when using FIND. Use SEARCH if case should be ignored, and consider cleaning the source text with TRIM if extra spaces are present.
Can I check every row in two columns at once?
Enter the formula for the first row, such as =ISNUMBER(SEARCH(B2,A2)) in C2, then drag the fill handle down. WPS Spreadsheet adjusts the row references automatically.
