Deleting Dates within Text Strings

by Allen Wyatt
(last updated June 11, 2016)

6

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Printing Selected Cells by Default

Want a one-button approach to printing? Excel provides the Quick Print tool, but it may not do exactly what you want. This ...

Discover More

Creating and Using Standardized Tables

If you have a common table layout that you want to use again and again, you'd benefit by having an easy way to save that ...

Discover More

Understanding the If ... End If Structure

One of the most basic of programming structures is the conditional structure: If ... End If. This tip explains how this ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Finding Cells Filled with a Particular Color

Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of ...

Discover More

A Fast Find-Next

Tired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do what ...

Discover More

Replacing Characters at the End of a Cell

The Find and Replace capabilities of Excel can come in handy, but they can't accomplish all your replacement needs. One such ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2017-06-20 10:17:27

Dennis Costello

The RE solution is indeed very interesting and this was a good opportunity for Allen to discuss it, but unfortunately it would remove strings that are vaguely similar to dates, but really aren't - and miss some that are. For instance, "98/56/34" and "15-1/2" would be removed, but "1/2/2017" would not, nor would "15-Apr-2016", "April 16, 2016", or any of all sorts of other strings that validly represent dates. The Split and IsDate approach avoids these problems (well, not the last example, which has spaces in the middle of the date and would therefore span multiple words). If the stripping out of extra blanks is a problem, I suggest that instead of building the value back up word by word, you use the Find function to give the starting position of the date string within the cell, Len for its length, and Mid to strip it out. This does leave one more problem, however - punctuation. Imagine the cell contains "The test started on 1/4/16, but did not succeed" - or, even worse, "The test started on 1/4/16,but did not succeed". In these cases, the words being matched are "1/4/16," or "1/4/16,but" - and neither of these would be picked up by IsData. So for these general cases you need a more sophisticated parser than simply splitting into words by a space.Bottom line - this is a hard and interesting problem.


2017-06-20 10:15:41

Dennis Costello

The RE solution is indeed very interesting and this was a good opportunity for Allen to discuss it, but unfortunately it would remove strings that are vaguely similar to dates, but really aren't - and miss some that are. For instance, "98/56/34" and "15-1/2" would be removed, but "1/2/2017" would not, nor would "15-Apr-2016", "April 16, 2016", or any of all sorts of other strings that validly represent dates. The Split and IsDate approach avoids these problems (well, not the last example, which has spaces in the middle of the date and would therefore span multiple words). If the stripping out of extra blanks is a problem, I suggest that instead of building the value back up word by word, you use the Find function to give the starting position of the date string within the cell, Len for its length, and Mid to strip it out.

This does leave one more problem, however - punctuation. Imagine the cell contains "The test started on 1/4/16, but did not succeed" - or, even worse, "The test started on 1/4/16,but did not succeed". In these cases, the words being matched are "1/4/16," or "1/4/16,but" - and neither of these would be picked up by IsData. So for these general cases you need a more sophisticated parser than simply splitting into words by a space.

Bottom line - this is a hard and interesting problem.


2016-06-19 15:27:38

Yvan Loranger

not a macro but 2 functions:
Assumes dates are surrounded by blanks [unless date is at start or end of text string] & text is in column A;hence following occupy colums B & C
=MID(A1,IF(FIND("/",A1)>2,IF(MID(A1,FIND("/",A1)-2,1)=" ",FIND("/",A1)-1,FIND("/",A1)-2),1),26)
=IF(MID(B1,LEN(B1)-2,1)="/",B1,LEFT(B1,FIND(" ",B1)-1))


2016-06-13 09:18:02

Mohit Gupta

I want a macro which finds the date in a particular text string and extracts it in a new cell.Can anyone help me.


2016-06-12 12:02:23

Yvan Loranger

A bit late but may be useful?
This handles single/double-digit days & months but dates must occur start/end of text string. Pls enter on 1 line.
=IF(FIND("/",A4)<4,
RIGHT(A4,LEN(A4)-FIND(" ",A4)+1),
LEFT(A4,FIND("/",A4) -
IF(mid(a4,FIND("/",A4)-2,1)=" ",
2,
3)
)
)


2016-06-11 09:32:15

Brian Lair

I had no idea that Excel was able to process regular expressions, so this was a great tip for me! Thanks!


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.