How to Leave Excel Cells Blank When the Status Is Rejected in WPS Office Spreadsheets
Question details
The user wants to convert status text such as Pending, Rejected, and Approved into numeric values while ensuring that Rejected records remain blank or return zero instead of a number.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is cleaning or transforming a status column so that approved and pending rows show the numeric value after the hyphen, while rejected rows do not produce a numeric value.
- Observed behavior
- Rejected status cells should appear blank or return zero, and other status cells should display the extracted numeric value.
Confirm that the status text follows a consistent pattern, such as "Approved-10" or "Rejected-5", and decide whether you want a live worksheet formula or a structured data transformation. Also locate the column that contains the combined status and value text.
Use a WPS Spreadsheets Formula to Blank Rejected Statuses and Extract Numeric Values
Best for a quick, non-destructive fix when you need to process existing cells immediately in a worksheet.
This formula checks whether the source cell contains the word "Rejected". If it does, the formula returns an empty string; otherwise it extracts the text after the hyphen and trims any extra spaces.
If your source text includes a closing parenthesis or other trailing character, remove or subtract that character from the extraction length.
Click the cell where you want the cleaned value to appear, such as B1 next to the first status text in A1.
Type =IF(ISNUMBER(SEARCH("Rejected",A1)),"",TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)))) and press Enter. The cell remains blank when A1 contains Rejected.
If the value carries a closing parenthesis, use =IF(ISNUMBER(SEARCH("Rejected",A1)),"",TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-1))) so the final character is not included.
Drag the fill handle down the column or double-click it to apply the formula to the rest of the status list.
Use Power Query to Return null for Rejected Rows in a Structured Query
Best for larger, repeatable data cleanup where the source table will be refreshed and you want a formal transformation step instead of worksheet formulas.
Clean Rejected Status Cells with WPS Office Spreadsheets
Use WPS Office Spreadsheets to apply the live formula directly in your worksheet, leaving Rejected statuses blank and extracting numeric values from Pending or Approved rows without relying on a separate Microsoft Office workflow.
- 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook that contains the status text, or create a new worksheet and paste the status values into column A.
- 2. Enter the formula in the adjacent column: Click cell B1 and enter =IF(ISNUMBER(SEARCH("Rejected",A1)),"",TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)))).
- 3. Fill the formula down: Drag the fill handle from B1 down to the last data row to process the complete status list.
- 4. Save as an Excel-compatible file: Click Menu > Save As, choose a location, and select .xlsx format to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
How do I return zero instead of blank for Rejected statuses?
Replace the empty string in the formula with 0, for example: =IF(ISNUMBER(SEARCH("Rejected",A1)),0,TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)))).
Can I use this formula in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the same IF, ISNUMBER, SEARCH, MID, FIND, and TRIM worksheet functions, so you can enter the formula directly in the worksheet.
What should I do if my status text uses a different separator?
Change the hyphen in FIND("-",A1) to the actual separator used in your data, such as a colon or comma, and keep the Rejected check unchanged.
Why does my formula include a trailing parenthesis or space?
If the original cell includes a closing parenthesis or extra character, subtract one in the MID length argument or wrap the MID result with SUBSTITUTE to remove that character.
Does Power Query work in WPS Office?
Power Query is a Microsoft Excel feature. For WPS Office Spreadsheets, use the worksheet formula approach; the workbook remains compatible with Excel.
