Deleting Dates within Text Strings

Karan has a lot of textual data in a workbook. Some of that textual data contains dates. She needs a way to remove any dates, but leave any other text that may be in a cell. For instance, what starts as "4/15/16 test ran successfully" or "test ran on 4/15/16" would be, after deletion, " test ran successfully" and "test ran on ".

If your dates always follow a simple pattern you could use the SUBSTITUTE function to remove them. For instance, if the date always consists of a single-digit month and a two-digit day, you could use the following to remove it:

=SUBSTITUTE(A1,MID(A1,SEARCH("?/??/??",A1),7),)

There are multiple problems with such a formula. First, it returns an error value if there is no date in cell A1 or if the date in cell A1 only has a single-digit day. Further, if the date has a two-digit month, it leaves that first digit intact (provided the date uses a two-digit day) or returns an error value (if the date uses a single-digit day).

If your date appears at the beginning or end of the text in a cell, you could use a different type of formula:

=IFERROR( IF( VALUE( LEFT( A1, 1)) > 0, RIGHT( A1, LEN(A1) - 7), ""), LEFT( A1, FIND( "/", A1) - 2))

The problem with this approach is that it also requires exact pattern M/DD/YY or MM/D/YY. It thus exhibits the same problems as the previous formula.

A more versatile approach is to use a macro to search for a date and then remove it from the string. The following example macro looks at a cell's conents and breaks it into "words" using the Split function. It then starts putting the words back together, as long as the word is not a date.

Function RemoveDates(ByVal vC As String)
    Dim arr As Variant
    Dim s As String
    Dim i As Integer

    RemoveDates = ""
    If vC > "" Then
        arr = Split(vC, " ")
        For i = LBound(arr) To UBound(arr)
            If Not IsDate(arr(i)) Then
                s = s & arr(i) & " "
            End If
        Next i
        RemoveDates = Left(s, Len(s) - 1)
    End If
End Function

You use the macro as a user-defined function in your worksheet, as follows:

=RemoveDates(A1)

The only drawback to this macro is that if you have multiple spaces between words, those spaces are removed in the replacement process. If this is a big deal to you, you might consider relying upon a regular expression in your macro, like this:

Function RemoveDates(MyRange As Range) As String
    Dim sRaw As String
    Dim sPattern As String
    Dim regEx As New RegExp

    sRaw = MyRange.Value

    sPattern = "[0-9]{1,2}[-.\\/][0-9]{1,2}[-.\\/][0-9]{2}"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .pattern = sPattern
    End With

    If regEx.Test(sRaw) Then
        RemoveDates = regEx.Replace(sRaw, "")
    Else
        RemoveDates = "Not matched"
    End If
    Set regEx = Nothing
End Function

This function is used in your worksheet in the same way as the earlier user-defined function:

=RemoveDates(A1)

The use of regular expressions may seem like a black art to some, but this particular usage is rather straightforward. The pattern (stored in the sPattern variable) says that one or two digits followed by a divider character (dash, period, backslash, or forward slash) followed by one or two more digits followed by another divider character followed by two digits is considered a date. If such a match is found in the cell, it is removed.

In order to use the regular espressions approach, you'll need to make sure that you include a referent to the following library in the Visual Basic Editor (choose References from the Tools menu):

Microsoft VBScript Regular Expressions 5.5

You should note, as well, that the function returns "Not matched" if a date was not located in the cell you reference.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7768) applies to Microsoft Excel 2007, 2010, 2013, and 2016.