How to Extract Month and Year Codes from Text in Excel
Question details
The user needs to extract specific month and year combinations (like JAN 23) from within complex text strings in an Excel worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing text data that contains embedded date abbreviations (three-letter month and two-digit year) within a predefined date range.
- Observed behavior
- The goal is to automatically search through text and isolate specific month-year codes without manually reading and retyping the dates.
Determine the specific date range you need to extract (e.g., January 2022 to December 2023) and verify that the target text consistently follows the three-letter month and two-digit year format separated by a space (mmm yy).
Use the LET and FILTER Formulas to Extract Month-Year Codes
Create a dynamic array formula that generates a sequence of valid month-year combinations and searches the text string for them.
This method uses the LET function to build a virtual list of months, then searches the target cell for any matching date codes. It is highly effective for datasets where dates fall within a known timeframe.
Click on the cell where you want the extracted date code to appear, for example, cell B2.
Type the following formula: =LET(dtsq,TEXT(EDATE(DATE(2022,1,1),SEQUENCE(24,1,0,1)),"mmm yy"),ARRAYTOTEXT(FILTER(dtsq,ISNUMBER(SEARCH(dtsq,A2)))))
Press Enter to execute the formula. Then, click the small square at the bottom-right corner of the cell and drag it down to fill the formula across your entire dataset.
Extract Dates Using Power Query for Large Datasets
Use Power Query for a robust, formula-free approach when dealing with extensive datasets that require frequent updates.
Seamless Data Extraction and Analysis in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like LET, FILTER, and SEQUENCE, allowing you to seamlessly extract dates and manipulate text data exactly as you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the Excel file containing your text data.
- 2. Enter the formula: Click the cell next to your source data and input the exact LET and SEQUENCE formula used for extraction.
- 3. Fill the data: Press Enter, then double-click the fill handle to apply the extraction formula to all rows in your dataset effortlessly.
Frequently Asked Questions
Why does the formula return an error if no date is found in the text?
If the text does not contain a valid month-year combination within your defined sequence, the FILTER function may return a #CALC! error because the filtered array is empty. You can wrap your entire formula in an IFERROR function to return a blank cell instead, like this: =IFERROR(LET(...), "").
Can I extract full month names and four-digit years instead?
Yes. You just need to change the date format code in the TEXT function portion of the formula from "mmm yy" to "mmmm yyyy". Ensure that the text strings you are searching also contain dates in this exact full format.
Does this formula distinguish between uppercase and lowercase letters?
No, the SEARCH function used in the formula is case-insensitive. It will successfully identify and extract 'JAN 23', 'Jan 23', or 'jan 23' without requiring any modifications to the formula.
