RE: Syntax to combine MID() and FIND("LastPost:") to extract the Last
- From: OssieMac <OssieMac@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 13 Dec 2009 22:59:01 -0800
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
.
- References:
- Prev by Date: Re: how do i move columns on one sheet to columns on another sheet
- Next by Date: Re: Printing Only Rows With Values In Them Options
- Previous by thread: Syntax to combine MID() and FIND("LastPost:") to extract the Last Post Date ?
- Next by thread: Re: Syntax to combine MID() and FIND("LastPost:") to extract the Last Post Date ?
- Index(es):
Relevant Pages
|
