Copy dates mixed among text in cells

[Dmichaud61]

I inherited an Excel 2016 spreadsheet with over 1300 rows with 1000s of dates mixed among text in cells in column E. The dates are formatted several different ways. For example, 30NOV21, 30 NOV 21, 30 November 21, and 30 November 2021. The dates range many days, months, and years. Is there a way to highlight just the dates and not the text string, or copy the dates to the adjacent cell in column F? Thanks in advance.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out (possibly over-simplified) sample data to test with.

See if this macro helps.

Sub Highlight_Dates()
  Dim RX As Object
  Dim a As Variant, m As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "(\D|^)(\d{1,2})([ \-\.]?)([a-z]{3,})([ \-\.]?)(\d{2,4})(\D|$)"
  Application.ScreenUpdating = False
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      For Each m In RX.Execute(a(i, 1))
        If IsDate(m.submatches(1) & " " & m.submatches(3) & " " & m.submatches(5)) Then .Cells(i).Characters(m.firstindex + 1, m.Length).Font.Color = vbRed
      Next m
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Here is the result of the macro on my sample data (I have used an image since XL2BB does not deal with different colour fonts within a cell.)

 

[Dmichaud61]

Peter - Many thanks your code worked perfectly. Also, I updated my profile as you suggested.

 

[Peter_SSs]

Peter - Many thanks your code worked perfectly.

You're welcome. Thanks for the follow-up.

Also, I updated my profile as you suggested.

Great! Thanks for that.