Excel jiggery-pokery

[DaveB]

OK, I could go and trawl Google for answers but this is easier and I'm feeling lazy.

I have a column in an excel file, each cell of which holds the contents of a free text field exported as a report from a ticketing tool.

The field contains multiple entries separated by a time stamp using a uniform format. Each entry may or may not contain multiple lines and line breaks.

Can anyone suggest a way to extract the most recent entry from each cell and put them somewhere else in their own cell.

12-10-2018 14:12:09 - Joe Bloggs (Customer Updates)
No change this week as project owner has been on leave.

05-10-2018 08:24:24 - Jane Blogges (Customer Updates)
Supplier has produced a more detail proposal for the solution, including a move to an integrated service built on the exiting API's

Costs yet to be agreed. 

28-09-2018 14:21:35 - Joe Bloggs (Customer Updates)
Discussions continue this week around the suitability of the proposed solution.  

There is still no clear plan for the project and no overall design document.

20-09-2018 11:24:55 -Joe Bloggs (Customer Updates)
While a number of meetings to discuss requirements and feasibility have been held no high level design has been proposed and a number of KDD's remain outstanding.  

17-09-2018 09:59:26 - Joe Bloggs (Customer Updates)
Ticket Accepted

[WTFH]

Will the posts always start with:

dd-mm-yyyy HH:mm:ss - <name> <(note)>

Also, is there a ^p at the end of the first line?
Will the top record always be the most recent?
Is there a risk that someone would type a date into the free text in the same format as the system is generating it?
Do you need the date of the most recent update in a separate field, or should it just be ignored and only the text returned?
Do you have any control over how the data is extracted from the ticketing tool?

My initial thought is to write a macro that splits the text based on finding "nn-nn-nnnn nn:nn:nn"

[DaveB]

Will the posts always start with:

dd-mm-yyyy HH:mm:ss - <name> <(note)>

Also, is there a ^p at the end of the first line?
Will the top record always be the most recent?
Is there a risk that someone would type a date into the free text in the same format as the system is generating it?
Do you need the date of the most recent update in a separate field, or should it just be ignored and only the text returned?
Do you have any control over how the data is extracted from the ticketing tool?

My initial thought is to write a macro that splits the text based on finding "nn-nn-nnnn nn:nn:nn"

Yes. dd-mm-yyyy HH:mm:ss - <name> <(note)> is auto text generated by the tool

No Idea.

Yes.

Possible but highly unlikely.

Nice to have, but not essential. Or return the entire block including time stamp.

No. Only option is "export to Excel" which produces the format above.

My thinking was find first instance of dd-mm-yyyy HH:mm:ss then extract all text up until next instance of dd-mm-yyyy HH:mm:ss and stick it into a new cell but my Regex / Excel skills are somewhat rusty.

[BoredBloke]

As was said above, a macro is probably your best bet.

You could assume that the line with the time, date and name will have at least 2 : and 3 - and use a formula like this

=IF(AND(LEN(O2) - LEN(SUBSTITUTE(O2, ":","")) >=2, LEN(O2) - LEN(SUBSTITUTE(O2, "-","")) >=3), TRUE, FALSE) to determine each new entry. Then you cane decide what you want to do under the true and false states. But the problem comes in how you'd capture the comments below these lines as the comments can be on multiple lines.

[LondonManc]

Load it into SQL Server, tag up all rows below and including a date row with that date stamp, give them a "number in datestamp" id and your last non-blank row in each datestamp is your required row. You (erm, I) could probably build it in SSIS in under an hour.

[DaveB]

Load it into SQL Server, tag up all rows below and including a date row with that date stamp, give them a "number in datestamp" id and your last non-blank row in each datestamp is your required row. You (erm, I) could probably build it in SSIS in under an hour.

No Access to SQL Server unfortunately.

[LondonManc]

No Access to SQL Server unfortunately.

OK. Looking at the rows above, what is your desired output?

[SimonMac]

Do you want elegant or quick and dirty?

Is each item in a single string in one cell?

=LEFT(%CELL%,19) will give you just the date and time
=MID(%CELL%,19,9999) will give you all the characters except the first 19

[DaveB]

OK. Looking at the rows above, what is your desired output?

12-10-2018 14:12:09 - Joe Bloggs (Customer Updates)
No change this week as project owner has been on leave.

Essentially everything from the first time stamp, which is always the first entry in the field, to the next time stamp down.

The lines above are all from the same cell in the spreadsheet, they are not individual rows.

[BoredBloke]

Sub StripData()
Dim ws As Worksheet
Dim lLR As Long
Dim lRowCounter As Long
Dim sVal As String
Dim sDate As String
Dim sTime As String
Dim sName As String
Dim sComment As String
Dim sTest1 As String
Dim sTest2 As String
Dim lOutRow As Long


lOutRow = 2

Set ws = ThisWorkbook.Sheets(1)
lLR = ws.UsedRange.Rows.Count
For lRowCounter = 2 To lLR
sVal = ws.Cells(lRowCounter, 1)
sTest1 = Replace(sVal, "-", "")
sTest2 = Replace(sVal, ":", "")
If Len(sVal) - Len(sTest1) >= 3 And Len(sVal) - Len(sTest2) >= 2 Then
sComment = ""
lOutRow = lOutRow + 1
sDate = Left(sVal, 10)
sTime = Mid(sVal, 11, 9)
sName = Right(sVal, Len(sVal) - 19)
ws.Cells(lOutRow, 3) = sDate
ws.Cells(lOutRow, 4) = sTime
ws.Cells(lOutRow, 5) = sName
Else
If sVal <> "" Then
sComment = sComment & Chr(10) & sVal
ws.Cells(lOutRow, 6) = sComment
End If
End If
Next lRowCounter

Set ws = Nothing


End Sub
raw data goes in column A on sheet 1 and output gets dumped to columns CDE&F on the same sheet