extract Date from string in Cell vba

[HebertsYES]

I have a cell that contains the following info:

Thu, Jan 29, 2015 at 9:33 PM, windyknollgoldens@gmail.com <windyknollgoldens@gmail.com> wrote:

The format will always stay the same, the date and email address will change.
what I need is to extract only the date from this string and put it into another cell. I do not want the time.
Can anyone tell me what vba code to use for this?</windyknollgoldens@gmail.com>

 

[JLGWhiz]

Select one of the cells and try running this.

Sub t()
Dim dt As Variant, myDate As string
dt = Split(ActiveCell.Value, ",")
myDate = dt(1) & ", " & dt(2)
MsgBox myDate
End Sub

 

[hippiehacker]

day

=MID(A1;FIND(" ";A1;FIND(" ";A1;1)+1)+1;2)

month

=MONTH(DATEVALUE(TRIM(MID(A1;FIND(", ";A1;1)+1;FIND(", ";A1;FIND(", ";A1;1))))&" 1 "))

year

=TRIM(MID(A1;FIND(", ";A1;FIND(", ";A1;1)+1)+1;5))

 

[Rick Rothstein]

Here is another way to do it with VBA code...

Sub Test()
  Dim Text As String, MyDate As String
  Text = ActiveCell.Value
  MyDate = Trim(Mid(Left(Text, InStr(Text, " at ")), InStr(Text, ",") + 1))
  MsgBox MyDate
End Sub

The above code returns the date as a text string, but if you want it to return a real date (which you can use the Format function on to display it in the form you want...

Sub Test()
  Dim Text As String, MyDate As Date
  Text = ActiveCell.Value
  MyDate = CDate(Mid(Left(Text, InStr(Text, " at ")), InStr(Text, ",") + 1))
  MsgBox Format(MyDate, "mmm d, yyyy")
End Sub