How to Automatically Extract Rows Beginning with Order Placed on PO in WPS Spreadsheets
Question details
The user needs to automatically extract all nonblank rows from column A that begin with 'Order placed on PO' and return them as a dynamic list in column C.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- They paste order tracking data into column A and need matching entries to appear in column C without manual filtering.
- Observed behavior
- The goal is a spill formula that updates automatically and excludes blank cells; manually filtering or copying does not keep the list current.
Make sure your WPS Office version supports dynamic array functions such as LET, FILTER, and LEFT. Select C1 and verify that the cells below are empty so the spilled results can expand.
Use the LET and FILTER dynamic array formula
Best for a single formula that removes blanks and extracts every row beginning with 'Order placed on PO' automatically.
This formula first creates a noBlank array from all nonempty cells in column A, then filters that array to entries whose first 15 characters equal 'Order placed on'.
Open the workbook in WPS Spreadsheets and click cell C1, where you want the extracted list to start.
Type or paste =LET(noBlank,FILTER(A:A,A:A<>""),FILTER(noBlank,LEFT(noBlank,15)="Order placed on")) into the formula bar.
Press Enter. WPS Spreadsheets automatically fills column C with matching entries and ignores blank cells in column A.
Add or remove order rows in column A to confirm the column C list expands or shrinks without editing the formula.
Use a compact FILTER formula without LET
A shorter alternative when you prefer one FILTER statement and your source data is not extremely large.
Use WPS Spreadsheets to Extract Order Placed Rows Automatically
WPS Spreadsheets supports the same dynamic array functions you would use in Excel, so you can build a spill formula that automatically lists every column A entry beginning with 'Order placed on PO' without VBA or manual filtering.
- 1. Download and install WPS Office: Go to the WPS Office download page, install the application, and open WPS Spreadsheets.
- 2. Open your order workbook: Use File > Open to load the workbook containing order data in column A.
- 3. Enter the formula in C1: Click cell C1 and type the dynamic array formula, or paste the LET/FILTER version from this page.
- 4. Press Enter and review the extracted rows: Press Enter to spill all matching rows into column C, then verify that blank cells are excluded.
- 5. Save in Excel-compatible format: Use File > Save As and choose .xlsx format to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Why does the formula return #SPILL! in WPS Spreadsheets?
#SPILL! usually means something is already in the cells below C1. Clear or move that content so the dynamic array has enough empty cells to display the full extracted list.
Will this formula work if my order data is pasted as text or CSV?
Yes. The formula compares the first 15 characters as text, so it works for pasted text, CSV imports, or typed entries as long as the phrase begins in column A.
How do I make the match case-insensitive?
Use UPPER or LOWER in the condition, for example =LET(noBlank,FILTER(A:A,A:A<>""),FILTER(noBlank,LEFT(UPPER(noBlank),15)="ORDER PLACED ON")).
Does the formula update automatically when I add more data?
Yes. Because the formula references A:A, WPS Spreadsheets recalculates and adjusts the spilled range automatically when rows are added, deleted, or edited.
Can I extract only rows that start with 'Order placed on PO' and also meet a second condition?
Yes. Add another filter condition, such as =LET(noBlank,FILTER(A:A,A:A<>""),FILTER(noBlank,(LEFT(noBlank,15)="Order placed on")*(RIGHT(noBlank,4)="PO"))).
