Extract Date from Text String - Format DD.MM

[jpagett9]

Hi guys,

I have a column with comments/text string in each cell and I am looking to pull just the date, in the format "DD.MM" using some sort of VBA code. The dates do not always occur at the start of the comment/text string. Sometimes in the middle also.

Is there any way to do this?

Thanks all!

 

[jpagett9]

To be clear, the date format that appears in the text string is "DD.MM" . All in one column

 

[pgc01]

Hi
Welcome to the board

2 or 3 examples of the text in the cells would help.
That would answer, for ex., is the dot in DD.MM is the only dot in the text?

 

[jpagett9]

Hi,

So for example, in cell AW2 of my sheet, I may have the following:

30.11 GG - Spoke to Jess, she is unavailable at this time.
28.10 CG - Tried calling but was unable to reach. Need to try email at jess.bloggs@maill.co.uk

There is usually more than one line of information per cell (like above). Furthermore, there is usually more than one dot also. Unfortunately, it is entered manually, so there is inconsistency between formatting, which is why I am having issues extracting the dates.

Thanks a lot for your help

 

[Rick Rothstein]

Those, of course, are not real dates, so I am not sure what you want in the cells they are pulled out to. In the following macro, I pull them out exactly as they are, but as numbers custom formatted as "00.00". If this is not correct, please clarify what you want in some detail (so we do not have to guess)...

Sub GetDates()
  Dim R As Long, X As Long, LastRow As Long, Parts() As String
  LastRow = Cells(Rows.Count, "AW").End(xlUp).Row
  For R = 1 To LastRow
    Parts = Split(Cells(R, "AW").Value, vbLf)
    For X = 0 To UBound(Parts)
      Cells(R, "AX").Offset(, X).Value = Val(Parts(X))
      Cells(R, "AX").Offset(, X).NumberFormat = "00.00"
    Next
  Next
End Sub

 

[pgc01]

Hi

In your examples the date is the first 5 characters.

For that case, you can use a simple formula:

=DATE(2017,MID(A1,4,2),LEFT(A1,2))