Copy dates mixed among text in cells

Dmichaud61

Board Regular
Joined
Mar 7, 2014
Messages
67
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Help.PNG
    Help.PNG
    39.1 KB · Views: 5

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.)

1617697665510.png
 
Last edited:

Dmichaud61

Board Regular
Joined
Mar 7, 2014
Messages
67
Office Version
  1. 2016
Platform
  1. Windows
Peter - Many thanks your code worked perfectly. Also, I updated my profile as you suggested.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,258
Messages
5,641,151
Members
417,195
Latest member
Vishal kumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top