How to Sort Date-Stamped Text Lines Within a Single WPS Spreadsheets Cell
Question details
The user needs to sort multiple line-separated entries stored in one WPS Spreadsheets cell so the most recent date appears first, even when entries cross into a new year.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A single cell contains several text lines, each beginning with a date and a colon, and the user wants to reorder them by date without manually cutting and pasting.
- Observed behavior
- The entries remain in their original order, and text-based sorting can put dates in the wrong order when the list spans more than one year.
Make sure each line in the cell uses the same date format before the colon, and that the lines were created with Alt+Enter line breaks rather than spaces. The destination cell should have Wrap Text enabled so the returned line breaks are visible.
Sort In-Cell Date-Stamped Lines with a Dynamic Array Formula
Use this when you have a current WPS Office version with LET, TEXTSPLIT, TEXTBEFORE, and SORTBY; it sorts by real date values and returns all lines in one cell.
The formula splits the cell at each line break, extracts the date before the colon, converts the date to a serial number, sorts the lines in descending order, and joins them back with line breaks. Sorting by serial number is what correctly handles lines that cross into a new year.
Click an empty cell next to the source data, such as B1.
Type =LET(lines,TEXTSPLIT(A1,,CHAR(10)),dates,DATEVALUE(TEXTBEFORE(lines,":")),TEXTJOIN(CHAR(10),,SORTBY(lines,dates,-1))) and press Enter. Replace A1 with the cell that contains your date-stamped lines.
Select the result cell, go to the Home tab, and click Wrap Text so the line breaks display correctly.
Copy the result cell, right-click the original cell or another destination, choose Paste Special > Values, and then delete the temporary formula cell.
Sort Date-Stamped In-Cell Lines in WPS Office Spreadsheets
You can sort date-stamped text lines inside a cell directly in WPS Office Spreadsheets using a dynamic array formula that splits lines, reads the date before each colon, sorts by real date values, and joins the lines back with line breaks.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains the cell with date-stamped lines.
- 2. Select the result cell: Click an empty cell next to the source cell, such as B1, where you want the sorted lines to appear.
- 3. Enter the sorting formula: Enter =LET(lines,TEXTSPLIT(A1,,CHAR(10)),dates,DATEVALUE(TEXTBEFORE(lines,":")),TEXTJOIN(CHAR(10),,SORTBY(lines,dates,-1))) and press Enter. Replace A1 with your actual cell.
- 4. Enable Wrap Text: With the result cell selected, go to the Home tab and click Wrap Text so each sorted line appears on its own line.
- 5. Paste as values if required: Copy the result cell, right-click the destination, choose Paste Special > Values, and then delete the formula column if you no longer need it.
Frequently Asked Questions
Will this formula sort by date correctly when my dates use two-digit years?
It uses DATEVALUE, so the date format must be unambiguous in your regional settings. For best results use four-digit years in every line so WPS can parse dates consistently across year boundaries.
Why does the result show #NAME? after I enter the formula?
That usually means your WPS Office version does not yet have LET, TEXTSPLIT, TEXTBEFORE, or SORTBY. Update WPS Office to the latest version or use a helper-column method.
Can I sort the lines without putting the formula in another cell?
A formula cannot reorder text inside the same cell directly. Enter the formula in a helper cell, then copy and paste the result over the original cell using Paste Special > Values.
Do I need to press Ctrl+Shift+Enter?
No. In WPS Office versions that support dynamic arrays, enter the formula normally with Enter. In older non-dynamic versions, this specific formula will not work.
What should I do if my text contains extra colons?
The formula uses the first colon as the separator between date and text. If extra colons appear later in the line, they remain part of the text because TEXTBEFORE reads only before the first specified colon.
