Find dates and delete text VBA

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
81
Hi gurus,

I am looking for a VBA solution to check a cell which contains several updates and only keep the latest update, each update has its updated date and time. See the example below:

M
22018-05-18 01:02:09 - K (Work notes)
As per suggestion, implemented the fix. No reoccurrence of the issue so far.

2017-11-08 21:25:51 - E (Work notes)
Stopped replication batch jobs
Stopped the replication models using - /SAPAPO/REPL_STOP
Reactivate the replication models using the program - /SAPAPO/OM_REPL_MODEL_EXT
Checked with business user for any issues while performing Single Click PRF – No issues
Repeated the failed job to success.
Restarted the replication batch jobs. So far no ABAP dumps and no new issues were noticed.

2017-11-06 03:08:48 -Y (Work notes)
Hi Team,,

Created high priority SAP message with SAP -

551624 / 2017 COMMIT/ROLLBACK error on DEFAULT Database Connection

<tbody>
</tbody>
Appreciate any assistance.

Thank you,
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Are you dates always in descending date order as shown in your example? If so, then you want to delete everything after the first date's information, correct?
 

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
81
Hi Rick,

Thanks for taking the time to review my question. Yes, the dates will always be in descending order and yes, I want to keep only the latest update and remove everything else after the second date is found.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Thanks for taking the time to review my question. Yes, the dates will always be in descending order and yes, I want to keep only the latest update and remove everything else after the second date is found.
I think this macro may do what you want...
Code:
Sub LeaveInfoForLatestDateInCellM2()
  Dim N As Long, Result As String, Info() As String
  Info = Split(Range("M2").Value, vbLf)
  Result = Info(0)
  For N = 1 To UBound(Info)
    If Left(Info(N), 10) Like "####-##-##" Then
      Exit For
    Else
      Result = Result & vbLf & Info(N)
    End If
  Next
  Do While Right(Result, 1) = vbLf
    Result = Left(Result, Len(Result) - 1)
  Loop
  Range("N2") = Result
End Sub
 

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
81

ADVERTISEMENT

That was absolutely awesome, thanks a lot for your quick assistance and for the code, this made the trick.
 

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
81
Sorry to ask you again, but how can I do to run this code along the column until the last cell containing text?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Sorry to ask you again, but how can I do to run this code along the column until the last cell containing text?
This should do it for you...
Code:
Sub LeaveInfoForLatestDateInCellM2()
  Dim R As Long, N As Long, Result As String, Info() As String
  For R = 2 To Cells(Rows.Count, "M").End(xlUp).Row
    Info = Split(Cells(R, "M").Value, vbLf)
    Result = Info(0)
    For N = 1 To UBound(Info)
      If Left(Info(N), 10) Like "####-##-##" Then
        Exit For
      Else
        Result = Result & vbLf & Info(N)
      End If
    Next
    Do While Right(Result, 1) = vbLf
      Result = Left(Result, Len(Result) - 1)
    Loop
    Cells(R, "N").Value = Result
  Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,451
Messages
5,601,727
Members
414,470
Latest member
glukemey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top