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

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.
0
u473
12/14/2009 1:43:43 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
868 Views

Similar Articles

[PageSpeed] 0

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



-- 
Regards,

OssieMac


0
Utf
12/14/2009 6:59:01 AM
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

-- 
Rick (MVP - Excel)


"u473" <u473@aol.com> wrote in message 
news:7cd6acc6-5c94-4f3a-9ba8-ce943a632fcc@j4g2000yqe.googlegroups.com...
>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. 

0
Rick
12/14/2009 9:36:13 AM
Thank you both for the two approaches
You made my day.
J.P.
0
u473
12/14/2009 1:10:49 PM
Reply:

Similar Artilces:

Syntax to combine MID() and FIND("LastPost:") to extract the Last Post Date ?
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("Sheet...