How to Split a Text Range into Start and End Dates in Excel
Question details
The user needs to extract a start date and an end date from a single continuous text string (e.g., "Jun 06 - Jun 21, 24") and distribute them into two separate date-formatted cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data cleaning and formatting where raw date range data is imported or entered as continuous text strings, making it impossible to perform standard date-based calculations or sorting.
- Observed behavior
- The start and end dates are bound together in a single text string, preventing standard spreadsheet tools from recognizing them as separate, valid date serial numbers.
Ensure that the text date ranges in your dataset follow a consistent structural pattern (such as 'Month Day - Month Day, Year') so that a single formula can accurately process the entire column without generating #VALUE! errors.
Use Modern Text Extraction Formulas (TEXTBEFORE, TEXTAFTER)
This is the most robust method for modern Excel users. It utilizes the LET function alongside TEXTBEFORE and TEXTAFTER to dynamically isolate the month, day, and year components, safely converting them into distinct start and end date serial numbers.
By defining variables within the LET function, you can cleanly parse the text around the hyphen (-) and comma (,) separators. The DATEVALUE function is then used to convert these extracted string pieces into actual numerical dates.
Select the empty cell where you want the start date to appear. Assuming your original text is in cell B2, enter the following formula: =LET(md,TEXTBEFORE(B2," -"),d,TEXTAFTER(md," "),m,TEXTBEFORE(md," "),y,TEXTAFTER(B2,", "),DATEVALUE(d&"-"&m&"-"&y)). Press Enter.
In the adjacent empty cell for the end date, enter the formula: =LET(md,TEXTAFTER(TEXTBEFORE(B2,","),"- "),d,TEXTAFTER(md," "),m,TEXTBEFORE(md," "),y,TEXTAFTER(B2,", "),DATEVALUE(d&"-"&m&"-"&y)). Press Enter.
Select the newly calculated cells containing the start and end dates. They may appear as standard numbers. Press Ctrl+1 to open the Format Cells dialog, select 'Date' from the Number tab, choose your preferred date format, and click OK. Finally, select the cells and drag the fill handle down to apply the formulas to the rest of your data.
Use Standard Text Functions for Older Versions (LEFT, RIGHT, FIND)
If your spreadsheet software does not support TEXTBEFORE or TEXTAFTER, you can extract the date parts using traditional string functions. This is best when the date format is strictly uniform, such as consistently using three-letter months and two-digit days.
Clean and Split Messy Date Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data cleaning utilities and a complete suite of text-manipulation functions. Whether you need to use advanced formulas or simple Text-to-Columns wizards, you can perfectly format complex date strings in seconds.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the column of combined date strings.
- 2. Apply text extraction formulas: Click into a blank adjacent column and input your preferred text extraction formula (e.g., using LEFT, RIGHT, and MID) to isolate the dates.
- 3. Format as dates instantly: Highlight your newly extracted columns, right-click and choose 'Format Cells', then apply your desired Date format to finalize your data cleaning process.
Frequently Asked Questions
Why does my extracted date formula return a #VALUE! error?
This usually occurs if the extracted text string cannot be recognized as a valid date format by your system's regional settings. Ensure the month abbreviation, day, and year are correctly concatenated with recognizable separators (like hyphens or slashes) before applying the DATEVALUE function.
Can I use the 'Text to Columns' feature to split dates instead of formulas?
Yes, you can use the Text to Columns wizard found under the Data tab. Select 'Delimited', set the hyphen (-) as your custom delimiter, and split the text into two columns. However, note that if the year is only located at the very end of the original string, you will need to manually append the year to the start date column afterward.
How do I fix formulas that stop working on dates with single-digit days?
If your original text sometimes features single-digit days (e.g., "Jun 6" instead of "Jun 06"), strict character-counting formulas like LEFT(E4,6) will extract incorrect data. It is highly recommended to use dynamic delimiter-based formulas using TEXTBEFORE, TEXTAFTER, or FIND to locate spaces and hyphens regardless of character length.
