To extract a date from text in Excel for Microsoft 365, isolate the date text and convert it with DATEVALUE. For example, if A2 contains Invoice date: 14 March 2025, enter =DATEVALUE(TEXTAFTER(A2,": ")) in another cell, then format that cell as a date. This returns a real Excel date, not just a string that looks like one.
What Excel Means by a Date in Text
Excel stores dates as numbers and displays them using a date format. Text that resembles a date is different: Excel may not recognize it as a date value, so sorting, date arithmetic, filters, and chart axes can behave unexpectedly. Extracting a date usually involves two separate tasks: locating the characters that make up the date, then converting those characters into an Excel date value.
The right approach depends on how consistent the source text is. A cell might contain a label followed by a date, such as Invoice date: 14 March 2025; a date between separators, such as Order 583 | 7 March 2025 | shipped; or a date in a fixed position. If the wording and separators are predictable, a formula can be concise. If the text varies widely, cleaning or reviewing the source data may be necessary.
Extract a Date After a Label
For Microsoft 365 on Windows, TEXTAFTER returns the text following a specified delimiter. With A2 containing Invoice date: 14 March 2025, enter =DATEVALUE(TEXTAFTER(A2,": ")) in B2. The extracted text is 14 March 2025, and the result is the date March 14, 2025, displayed according to the cell’s date format and regional settings.
This version assumes the date is everything after the colon and space. If A2 instead contains Invoice date: 14 March 2025, paid, the formula would pass the extra word paid to DATEVALUE and may return an error. If the date is enclosed by a known pair of separators, isolate both sides before converting it.
With A2 containing Order 583 | 7 March 2025 | shipped, enter =DATEVALUE(TEXTBEFORE(TEXTAFTER(A2,"| ")," |")) in B2. The formula takes the text after the first vertical bar and space, then keeps only the part before the next bar. It returns March 7, 2025, as a date value. The formula assumes that exact delimiter pattern and one date between the bars.
TEXTAFTER and TEXTBEFORE are available in Microsoft 365 and Excel 2024. If your Excel edition does not include them, use a compatible formula based on MID and FIND, or use the import tools described below.
Convert a Fixed-Position Date
When the date always starts at the same character position and has the same length, MID can extract it. Suppose A2 contains Ref 81; date=14-Mar-2025; status=open and the date always begins after date= and ends at the next semicolon. With that layout in A2, a formula such as =DATEVALUE(MID(A2,FIND("date=",A2)+5,11)) extracts 14-Mar-2025 and returns March 14, 2025.
This formula depends on the date label and the 11-character date format staying consistent. A different month spelling or a single-digit day can change the number of characters and make the result wrong. For formulas that depend on position, test examples with the shortest and longest dates in your data before filling the formula down.
For an ISO-style date stored by itself as text, such as 2025-03-07 in A2, you can build a date from its year, month, and day components. With that exact yyyy-mm-dd layout in A2, enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). It returns March 7, 2025. This avoids interpreting the month and day according to regional settings, provided the text always follows the stated format.
That fixed-layout formula is not a general-purpose validator. Excel’s DATE function can roll an out-of-range day into a later month, so a malformed source such as 2025-02-31 should be checked rather than assumed valid. Confirm the source format and inspect results when data comes from an external system.
Use Text to Columns or Flash Fill
For a one-time cleanup, a menu tool may be easier than writing a formula. If each cell has a consistent delimiter, Text to Columns can split the text into separate columns; then the date portion can be converted or formatted. Select the source cells and choose Data > Text to Columns. In the wizard, choose Delimited, select the delimiter used in the data, and complete the steps. Check the preview and choose a destination if you do not want to overwrite adjacent data.
Text to Columns splits content; it does not automatically understand every kind of date embedded in arbitrary prose. After the split, confirm that the date column contains actual date values. If the date is in a recognizable format, Excel may convert it during the split, but results can depend on the chosen date order and regional settings. Keep a copy of the original column until you have checked the output.
Flash Fill can help when you need a quick extraction and the pattern is easy to infer. Type the date you want from the first source cell in the neighboring column, begin typing the next example, and press Ctrl+E on Windows if Excel suggests the intended pattern. On Mac, the shortcut may vary by keyboard and Excel version; use Data > Flash Fill if available. Flash Fill produces filled values rather than a formula that updates with the source, so it is less suitable when the source text will change.
Choose a Method for the Data You Have
Before building a solution, check how regular the text is and whether the result needs to update automatically. These practical distinctions help avoid choosing a fragile method:
- Consistent label and delimiter: Use
TEXTAFTERand, if needed,TEXTBEFORE, followed byDATEVALUE. - Fixed-width, fixed-position text: Use functions such as
MID,LEFT, andRIGHT, then convert the extracted text. - One-off cleanup: Consider Text to Columns or Flash Fill, and keep the original text for checking.
- Irregular messages or several possible date formats: Standardize the source where possible, or use a reviewed set of rules instead of assuming one formula will interpret every case correctly.
For a recurring import, a formula is generally easier to recalculate when the source changes. For a static list, a tool may be quicker. If the text can contain multiple dates, decide whether you need the first date, the last date, or every date. A formula designed to return one date will not automatically extract all dates from a sentence.
Locale, Date Order, and Formatting
A common source of errors is the difference between month-day-year and day-month-year formats. The text 04/05/2025 can mean April 5 or May 4. DATEVALUE interprets text using the date conventions recognized by Excel and the system’s regional settings; it cannot know which meaning the writer intended when the source is ambiguous.
When possible, use unambiguous source dates, such as 5 April 2025 or the ISO-style 2025-04-05, and confirm the expected order with the data provider. Text to Columns lets you specify a date order for applicable formats during conversion. Do not simply change the display format to fix an interpretation error: formatting changes how a date value appears, not which day Excel stored.
After conversion, select the result cells and choose Home > Number Format, then select a date format. You can also use Ctrl+1 on Windows to open Format Cells and select a date format. On Mac, use Command+1 to open Format Cells. A cell displaying a number after conversion may simply be using General format; applying a date format changes its appearance without changing the underlying value.
Check Results and Troubleshoot Errors
Do not assume that a formula returning a value has found the intended date. Compare a sample of results against the original text, especially when data comes from multiple sources. A converted date can be checked by changing its number format temporarily or testing whether it behaves as a date in a calculation. For example, a correctly converted date can be sorted chronologically rather than alphabetically.
Common problems include:
- #VALUE!: The extracted text may include extra words, punctuation, or a date format Excel does not recognize. Refine the extraction boundaries or normalize the text first.
- Wrong day and month: The source is ambiguous and Excel interpreted it using a different regional order. Verify the intended convention before converting.
- Formula returns the whole remainder of the cell: The formula finds the starting delimiter but not the ending one. Add a reliable ending delimiter or extract a fixed number of characters only if the format is truly fixed.
- Dates do not sort chronologically: The results may still be text, or some rows may have failed conversion. Check representative cells and make sure every row uses compatible source formatting.
- Formula works in some rows only: The text pattern may vary, such as different labels, spacing, or date lengths. Identify those variations and handle them explicitly rather than assuming the first pattern applies everywhere.
Frequently Asked Questions
Can Excel automatically recognize a date inside a sentence?
Not reliably in every sentence. Excel may recognize a date when it is entered or imported in a familiar format, but it does not consistently identify and extract dates from unrestricted prose. Use a formula or cleanup method that matches the structure of your text, then verify the converted values.
Why does DATEVALUE return #VALUE!?
The text passed to DATEVALUE may include characters beyond the date, or the date may use a format that Excel does not recognize under the current regional settings. Check what the extraction formula returns by itself, remove unwanted text, and confirm that the remaining date is valid and unambiguous.
How do I extract a date when it is between commas?
Use the same delimiter method as for other separators: isolate the substring between the commas, then convert it with DATEVALUE. The exact formula depends on whether there are other commas before or after the date. Test it on a representative row and make sure the chosen delimiters identify the intended date, not another field.
Can I extract more than one date from a cell?
Yes, but the method depends on the number of dates and how they are separated. A formula built to return one date will not return every date automatically. If the count and separators are consistent, design an extraction for each position; for irregular text, splitting or reviewing the source may be more dependable.
Does changing the cell format convert text into a date?
No. A number format controls how an existing numeric date value is displayed. If the cell contains date characters stored as text, applying a date format alone usually will not convert them. Extract and convert the text first, then apply the display format you want.
Related
- Excel SUM with Multiple Criteria: How to Do It
- Excel: Subtract the Same Value From Multiple Cells
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation: Allow Unique List Values?No: an Excel data-validation dropdown does not, by itself, require unique choices or stop the same choice being…
- SUMIF Example Formula: Syntax, Uses, and Common FixesWith product names in A2:A7 and sales amounts in B2:B7, use =SUMIF(A2:A7,"Apples",B2:B7) to add the sales for Apples;…
- SUMIFS Excel Less Than: Criteria and ExamplesTo sum values in Excel only when a related value is less than a limit, use SUMIFS with…
- Count Unique Values in Excel with a Simple FormulaTo count distinct, nonblank entries in a column in Microsoft 365, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) in an empty cell, replacing…
- VLOOKUP Errors in Excel: Causes, Checks, and FixesMost VLOOKUP errors come from a lookup value that does not match the first column of the search…
- Excel SUM OFFSET: Create Flexible, Expanding TotalsIn Excel, combine SUM and OFFSET to total a range whose size or position can change: for example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.