Need to parse first 19 characters on the line in which a specific string is found

[bR851236574]

I need the first 19 characters of the line in which the first string of " (Additional comments)" is found.

The cell contains text with potentially multiple entries of work notes and/or additional comments with their associated date/time stamp. I only want the most recent date/time for additional comments.

Here's an example of one of the cells from my source data.  The way the data is stored, the most recent entry is always first.   As you can see the work notes are most recent.  I need it to return "06/19/2020 13:26:02".

Data Example:

06/24/2020 15:01:21 - Jane Smith (Work notes)

Adding: Build ECheck-in WQ for ASC

06/19/2020 13:26:02 - Josh Miller (Additional comments)

Tina Jones will add to the Front Desk Report

Attempts:

if Text.Contains([Comments and Work notes],"(Additional comments)") then Text.BeforeDelimiter([Comments and Work notes]," -") else ""

This works if an additional comment is most recent but returns 06/24/2020 15:01:21 when I need 06/19/2020 13:26:02.

if Text.Contains([Comments and Work notes],"(Additional comments)") then Text.BeforeDelimiter([Comments and Work notes]," (Additional comments)") else ""

Again, this works if additional comments are the most recent, otherwise it returns:

06/24/2020 15:01:21 - Jane Smith (Work notes)

Adding: Build ECheck-in WQ for ASC

06/19/2020 13:26:02 - Josh Miller

Unfortunately I can't capture x number of characters to the left of " (Additional comments)" as a delimiter because the name right before it varies in length and it's not in between 2 delimiters.  

 Any thoughts or suggestions?

[Anonymous]

try wrapping text.split  in try otherwise:

List.Transform(List.Select(try Text.Split([comments],"#(lf)") otherwise {}, each Text.Contains(_,"(Add")), each Text.Start(_,19)))

and good luck

[Anonymous · Accepted solution]

I hadn't read everything and I hadn't seen the case of many lines with "(Add ...".

the modified expression to handle this situation as required:

List.Max(List.Transform(
List.Select(try Text.Split([comments],"#(lf)") otherwise {},
each Text.Contains(_,"(Add")), each DateTime.FromText(Text.Start(_,19),"en-US"))))

I had to use the optional "en-US" parameter because the dates are in a different format from my country.
You probably don't need it.

[bR851236574]

@Anonymous That did it.  You're awesome!  Thank you so much.

[bR851236574]

@Anonymous Awesome, that fixed the null, did you see the issue I added with multiple additional comments?