How to Scan an Excel Range and Return Results When It Contains a Number in WPS Office
Question details
The user needs to check a range of cells for numeric values and return a text result when at least one number is found.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is creating a worksheet formula to scan a cell range and conditionally return a text result when any numeric value exists.
- Observed behavior
- The formula should return a concatenated text result when at least one number is found in the range; otherwise, it should return blank.
Make sure the target range, text cells, and result cell references in your sheet are correct before entering the formula. Confirm that your WPS Office version supports dynamic array functions such as TOCOL and REDUCE, or use the compatibility-friendly SUMPRODUCT method.
Use the REDUCE, TOCOL, and TEXTJOIN Formula to Scan the Range
Use this formula when you need to check an entire range for numeric values and return a joined text result only if at least one number is found.
This method converts the range into a single column with TOCOL, counts numeric cells with ISNUMBER and REDUCE, and uses IF to return the TEXTJOIN result only when the count is greater than zero.
Click the cell where you want the result to appear, such as D4.
Type =IF(REDUCE(0,TOCOL(B2:C8),LAMBDA(total,value,total+N(ISNUMBER(value))))>0,TEXTJOIN(" - ",TRUE,J1,G7,A7),"") in the formula bar.
Press Enter to run the formula. The cell returns the joined text when at least one numeric value exists in B2:C8; otherwise, it stays blank.
Use SUMPRODUCT with ISNUMBER for a Simpler Compatibility-Friendly Formula
Choose this method if your WPS Office version does not support TOCOL or REDUCE, or if you prefer a shorter formula that works in most spreadsheet versions.
Scan a WPS Spreadsheets Range and Return a Text Result When Numbers Exist
Use WPS Office Spreadsheets to enter the same Excel-compatible formula and conditionally return your text result. The formula scans the range with TOCOL and REDUCE or with SUMPRODUCT, so you can verify numeric values without manually checking each cell.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx or .xls file that contains the range you want to scan.
- 2. Select the result cell: Click the cell where you want the formula result to appear, such as D4.
- 3. Type the scan formula: Enter =IF(SUMPRODUCT(--ISNUMBER(B2:C8))>0,TEXTJOIN(" - ",TRUE,J1,G7,A7),"") in the formula bar and adjust the range and text cells to match your sheet.
- 4. Press Enter and verify the result: Press Enter to calculate. The cell returns the joined text when at least one numeric value is present in the scanned range.
Frequently Asked Questions
Does the TOCOL and REDUCE formula work in WPS Office Spreadsheets?
It works in WPS Office versions that support dynamic array functions. If you see a #NAME? error, update WPS Office or use the SUMPRODUCT and ISNUMBER formula, which is more broadly compatible.
How can I return a different message when no number is found in the range?
Replace the empty string at the end of the IF formula with your preferred text, for example =IF(SUMPRODUCT(--ISNUMBER(B2:C8))>0,TEXTJOIN(" - ",TRUE,J1,G7,A7),"No numbers found").
Can I scan multiple columns and rows at once?
Yes. Use a range reference such as B2:C8 in the formula. TOCOL or SUMPRODUCT processes the entire range, so you do not need to check each column separately.
Why does the TEXTJOIN function return J1, G7, and A7?
The formula uses TEXTJOIN to combine the values in cells J1, G7, and A7 with a separator when the scan finds at least one number. Change those cell references to return the text you need.
