How to extract date from string in Excel

In this guide, we’re going to show you how to extract date from string in Excel by using formulas and VBA.

Download Macro-Free Workbook

Download Workbook

Note that, there isn't a single formula or approach that can handle all scenarios. Extracting dates from strings that is not following a specific pattern is one of the few things Excel cannot handle. You almost need an AI to be able to encapsulate every possible scenario. The good thing is that we are going to offer you multiple ways to parse the date.

Data

We have generated strings that included dates using slash (/) as day-month-year separator and using US-type (month/day/year) dates. Thus, if your date format is different, you may see errors (empty cells because of IFERROR functions) after opening our file. Update the dates according to your localization settings. Also, do not forget to update the slash characters if your data includes dates with other separators like dot (.) or hyphen (-).

Formula for extracting date from string

The logic behind the formulas we will introduce is to locate the pattern that resembles a date. Once the potential date value's location is found, we can parse it easily.

The tricky part is to select a correct pattern which is suitable with your raw data. A date can be in many forms:

• 01/07/2022
• 1/7/2022
• 1/7/22
• 1/12/2022
• 12/1/2022
• 12/1
• 12/2022

If your raw data contains more than one or two patterns the formula solution may not work unless you do not want to create extremely long formulas.

In our sample formula, we used "01/07/2022" format. Thus, we assume the date values in the data to follow the rules below:

• US Date format
• Month and day are two digits
• Year uses four digits
• Each unit is separated by slash "/"
• A date contains 10 characters including delimiters

Based on these assumptions, we can use "/??/" keyword with the SEARCH function to locate the date. The SEARCH function can use the wildcards to locate a pattern instead of an exact string. Because a question mark (?) represents a single character, "/??/" refers 2 characters between slashes just like the day part of a date.

The SEARCH function returns the position number where the keyword starts if there is a matching string.

Subtract the position by 2 to find where the date starts.

Once the date is located, you can use the MID function to parse it. The MID function needs the position of the string you want to parse and its length. Because we assume the date format is "mm/dd/yyyy", the length will be 10.

Although, the formula looks like its working, it will return any characters matching the pattern i.e. letters.

This is where the DATEVALUE function comes handy. The function can convert text-based date/time values into actual dates. The function returns #VALUE! error if the supplied text is not a valid date. You can use the DATEVALUE function to verify dates. The IFERROR function can wrap the formula to cover scenarios without date.

Although this formula is suitable for our example, it's obvious that it will fail for others. A few examples:

• A single-digit month date without a space in front: cells C6, C9
• A text containing similar string before the actual date: cell C8
• Single-digit month and days: cells between C19 and C25
• Double-digit years: cells between C26 and C32

Using VBA for extracting date from string

An alternative to the Excel formulas is to create your own function by using VBA. The VBA-based functions, also known as user defined functions (UDF), can be used as regular formulas once they are written and saved properly.

VBA has two dedicated methods which give a particular advantage for extracting a date from a string.

1. The Split method can separate words or strings separated by spaces.
2. The DateValue method can validate dates better than its formula counterpart.

Obviously, this method is not foolproof neither. The date values should be separated from other text by a space character. On the other hand, you are not limited with a certain pattern. Thus, number of digits in units are not significant.

You can copy and paste the following code into a module and start using it right away. Just do not forget to save your Excel file as an Excel Macro-Enabled Workbook (XLSM) rather than a regular workbook.

Function ExtractDate(str As String)
    
    On Error GoTo errHandler
    Dim i As Integer, dt As Date, arr() As String, temp As String
    
    dt = 0
    arr = Split(str)
    
    For i = LBound(arr) To UBound(arr)
        temp = arr(i)
        If IsDate(temp) And Len(temp) > 5 Then
            dt = DateValue(temp)
            Exit For
        End If
    Next i

cont:
    ExtractDate = dt
    Exit Function
    
errHandler:
    dt = 0
    Resume cont
End Function

Once you paste the code, use it in your worksheet by giving a reference to the data cell.

The equals to zero (0) condition is to avoid zero (0) values. Otherwise, the function will return zero (0) when it cannot find a valid date.

As you can see either approach is far from perfection. However, you have more than one option to extract date from string.