Syntax to combine MID() and FIND("LastPost:") to extract the LastPost Date ?

[u473]

I want to organize my Tech Doc by Subject and Post Date.
I am looking to extract the Last post date, but I cannot really use
the right() function due to the date variable length.
I need to create a loop thru all rows in Column A, FIND "Last post:",
skip one character
and copy the next 12 characters for the Last post date in Column B
Column A contains one string of variable length.
Post header text.... - 5 posts - 2 authors - Last post: Nov 21,
2008
Post Header text.... - 2 posts - 2 authors - Last post: Nov 25
.......
Sub PostDate()
Set Sht =Sheets("Sheet1")
Dim X as Long
Dim LastRow as Long
LastRow = sht.Cells(sht.Rows.Count,1).End(XlUp).Row
For X = 1 to LastRow
Cells(X,2).text = MID(FIND("Last post:",.....,12) ???
'I need this syntax formatted as Date yyyy/mm/dd for sorting
purpose.
Next
End Sub

Help appreciated,
J.P.

 

[OssieMac]

Hi J.P.

Because it appears that you have a colon after Last post, the position of
the colon can be returned with the InstrRev function and subtract that from
the total length of the string to find how many characters are required in
the Right function to return the date.

DateValue can then be used to return an actual date. Because it is an actual
date, it will sort in either Ascending or Decending order of the dates and
there is no need to format as Y/M/D. However, I have included a line to show
you how to code the formatting of the date column. Edit the part between the
double quotes to change the format to any valid date format.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Also it will not surprise me if someone posts an answer with a formula on
the worksheet without the the VBA code.

Sub PostDate()
Dim sht As Worksheet
Dim rng As Range
Dim cel As Range
Dim strDate As String

Set sht = Sheets("Sheet1")

'Cells(2, "A") assumes you have column headers
'and data actually starts on row 2.
With sht
Set rng = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))

'Edit the date format to whatever suits you.
.Columns(2).NumberFormat = "mm/dd/yyyy"
End With

For Each cel In rng
strDate = Trim(Right(cel.Value, _
Len(cel.Value) - _
InStrRev(cel.Value, ":")))

cel.Offset(0, 1) = DateValue(strDate)
Next cel

End Sub

 

[Rick Rothstein]

Here is another approach for you to consider...

Sub PostData()
Dim Cell As Range
On Error Resume Next
With Worksheets("Sheet6")
.Columns("B").NumberFormat = "General"
For Each Cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
Cell.Offset(, 1).NumberFormat = "mm/dd/yyyy"
Cell.Offset(, 1).Value = Trim(Split(Cell.Value, ":")(1))
Next
End With
End Sub

 

[u473]

Thank you both for the two approaches
You made my day.
J.P.