How to Extract Start and End Dates from Mixed Text in WPS Office Excel
Question details
The user needs to extract transaction start and end dates from description text that mixes date ranges, full month names, and abbreviated month-year periods such as Aug22-Jul23.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Transaction descriptions contain dates in several formats and need to be separated into start and end date columns.
- Observed behavior
- The desired date values are embedded in free text. Recognizable patterns include numeric ranges, full month names, and month-year abbreviations, while ambiguous entries require a review flag.
Review the description column and identify which rows contain numeric ranges, full month names, or abbreviated month-year values such as Aug22-Jul23. Make sure the target start and end date columns are formatted as Date before you apply formulas.
Extract Dates by Pattern Classification and Formula Conversion
Use this formula-based method when descriptions have recognizable separators or month-year tokens and you need consistent, repeatable extraction.
Classify the text pattern first, then use TEXTBEFORE and TEXTAFTER to split clear ranges and DATE, EDATE, DATEVALUE, or VALUE to construct dates. Wrap the result in IFERROR so ambiguous rows are flagged instead of producing formula errors.
In WPS Spreadsheets, open your workbook, select the description column, and identify whether a row uses a numeric range such as 01/02/2024-05/02/2024, full month names such as January 15, 2024, or an abbreviated period such as Aug22-Jul23.
Click the start date cell in the first data row and enter =TEXTBEFORE(A2,"-") to get the text before the dash. In the end date cell enter =TEXTAFTER(A2,"-") to get the text after the dash.
For a token such as Aug22, click the target cell and enter =DATE(2000+RIGHT(B2,2),MATCH(LEFT(B2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),1). For an Aug22-Jul23 period, use =EOMONTH(start_date,11) to return July 31, 2023 as the end date.
Click the start date cell and enter =DATEVALUE(A2) when the text is a recognized date such as "January 15, 2024". If the text contains extra spaces, wrap it as =DATEVALUE(TRIM(A2)).
In a Review column, enter =IFERROR(DATEVALUE(TEXTBEFORE(A2,"-")),"Review") or wrap your extraction formula in IFERROR to return "Review" for ambiguous rows that need manual correction.

Extract Start and End Dates in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use the same Excel-compatible TEXTBEFORE, TEXTAFTER, DATE, EDATE, DATEVALUE, and IFERROR functions to split mixed text into start and end date columns. You can keep the formulas in XLSX files and share them with Excel users without extra conversion.
- 1. Open the mixed-text workbook: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the transaction descriptions.
- 2. Add start and end date columns: Click the first empty cell in the Start Date column, type =TEXTBEFORE(A2,"-") for a clear range, and press Enter. Then copy the formula down to the remaining rows.
- 3. Convert abbreviated and text dates: In the next target cell, apply DATE, EDATE, DATEVALUE, or VALUE as needed. For example, use =DATE(2000+RIGHT(B2,2),MATCH(LEFT(B2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),1) for Aug22-style tokens.
- 4. Flag rows that need review: Wrap the formula in IFERROR, such as =IFERROR(DATEVALUE(TEXTBEFORE(A2,"-")),"Review"), and filter the column to see ambiguous descriptions.
- 5. Save for Excel compatibility: Click Menu > Save As and choose Excel Workbook (*.xlsx) to keep the formulas compatible with Microsoft Excel.

Frequently Asked Questions
Which date formats can these WPS Office Excel formulas extract automatically?
They handle clear numeric ranges such as 01/02/2024-05/02/2024, full month names such as January 15, 2024, and abbreviated month-year tokens such as Aug22 when you combine TEXTBEFORE, TEXTAFTER, DATEVALUE, and DATE or EDATE.
How do I parse a start date from a value like Aug22-Jul23?
Use a month mapping formula for Aug22 to create August 1, 2022. For the end date, use =EOMONTH(start_date,11) to return July 31, 2023, or adjust the month offset if the period length is different.
What should I do if a description does not contain a recognizable date or dash?
Wrap the extraction formula in IFERROR to return "Review", then filter the Review column and correct those rows manually.
Do TEXTBEFORE and TEXTAFTER work in older WPS Office versions?
They are available in current WPS Office versions that support Excel 365 functions. If they are not available, split the range with FIND, LEFT, RIGHT, or MID and then convert the result with DATEVALUE.
Can I save the result as an Excel file after applying date formulas?
Yes. In WPS Spreadsheets, click Menu > Save As and choose Excel Workbook (*.xlsx). The formulas remain compatible with Microsoft Excel.




