To extract a date from a text string, you can use a formula based on the TOROW and REGEXEXTRACT functions.
How to extract date from a text string in Excel?
- Select cell C1.
- Type =TOROW(REGEXEXTRACT(B3,”(\d{2}/\d{2}/\d{4})”,1))
- Press Enter.
- The formula will return the sum of comma separated values.
Example
In this example, we have a dataset in range B3:B9 containing text strings with embedded dates in the format “MM/DD/YYYY.” The goal is to create a formula and extract the date from each text string and display it in column D.
The formula in cell D3 uses the REGEXEXTRACT function to identify and extract the date pattern from the text and the TOROW function to ensure the output is structured as a single value.
Formula:
=TOROW(REGEXEXTRACT(B3,”(\d{2}/\d{2}/\d{4})”,1))
The result looks great, the formula extracts dates from a text string.
Explanation
Evaluate the formula from the inside out:
REGEXEXTRACT(B3, “(\d{2}/\d{2}/\d{4})”, 1) extracts text from a string using a pattern defined by a regular expression (regex).
- \d matches any digit (0-9).
- {2} specifies exactly two digits must match (e.g., “03” or “15”).
- / matches the forward slash character literally.
- \d{4} matches exactly four digits (e.g., “2024”).
The pattern \d{2}/\d{2}/\d{4} matches dates in the “MM/DD/YYYY” format. Parentheses () create a capturing group to isolate this match. The “1” argument specifies extracting the first captured group.
For example, in B3 (“The spring concert…. on 03/15/2024 … main hall.”), this function extracts “03/15/2024”.
=TOROW(REGEXEXTRACT(B3,”(\d{2}/\d{2}/\d{4})”,1)) transforms the extracted result into a horizontal array (a single row). If the REGEXEXTRACT function extract multiple dates, TOROW ensures that these are returned as a row array, even though there is only one date in this case.
Using TEXTSPLIT to extract dates from a text string
You can use a formula based on the TEXTSPLIT, MID and FIND functions to extract a date from a cell contains text.
Formula:
=TEXTSPLIT(MID(B3, FIND(“/”, B3) – 2, 10), ” “)
Explanation:
FIND(“/”, B3) identifies the first forward slash’s position in the text from cell B3. The text “The spring concert will be held on 03/15/2024 in the main hall ” finds the first slash at position 29. This step pinpoints the date’s location within the text.
FIND(“/”, B3) – 2: Subtracting 2 moves the starting position 2 characters backward, ensuring that the function captures the month portion (MM) of the date.
MID(B3, FIND(“/”, B3) – 2, 10): The MID function extracts a substring starting from a specific position for a given length. Take a look at the arguments:
- B3: The text string.
- FIND(“/”, B3) – 2: The starting position, two characters before the first /.
- 10: The number of characters to extract.
For example, for cell B3, it extracts 03/15/2024.
=TEXTSPLIT(MID(B3, FIND(“/”, B3) – 2, 10), ” “): The formula splits the extracted substring into separate pieces using the specified delimiter, which is a space (” “). It removes any extra text surrounding the extracted date, ensuring only the clean date remains. For the text “The spring concert will be held on 03/15/2024 in the main hall.”, the formula extracts and returns “03/15/2024”.
Workaround with MID and SEARCH function
For non-Microsoft 365 users we provide a workaround with the MID and SEARCH functions.
Formula:
=MID(B3,SEARCH(“??/??/????”,B3),10)
Explanation:
SEARCH(“??/??/????”, B3) locates the position of the first date-like pattern (MM/DD/YYYY) in the text from cell B3. For the text “The spring concert will be held on 03/15/2024 in the main hall.”, it identifies the date starting at position 29.
MID(B3, SEARCH(“??/??/????”, B3), 10) extracts 10 characters from cell B3 starting at the position found by the SEARCH function. This step isolates the date “03/15/2024” from the text.