Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to combine MID() and FIND("LastPost:") to extract the LastPost 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to combine MID() and FIND("LastPost:") to extract the Last
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to combine MID() and FIND("LastPost:") to extract the Last Post Date ?
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine text from multiple cells into one cell - =(A1&","&A2","&A3 | Excel Worksheet Functions | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Extract text plus "n" characters or date on partial match | Excel Programming | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
How do I "extract" birthyear from a date field? | Excel Discussion (Misc queries) |