Extract Date and Comment from prepend log entry field

Question

I have created a formula field by piecing together answers from similar posts, but now need to take things a step further and can't seem to find the answer.

I have a notes field (Status Update) that is set to log field entries which prepends the newest entry at the top. The field displays as:

[DATE User] Comment

Additionally, the formula results in showing either the maximum of the three most recent entries, or 2 if only 2, or 1 if only 1, or displays "No Update" if none.

Original formula:
var text LINEONE = Trim(Part([Status Update],2,"["));
var text LINETWO = Trim(Part([Status Update],3, "["));
var text LINETHREE = Trim(Part([Status Update],4,"["));

If(Length($LINEONE) < 1 and Length($LINETWO) < 1 and Length($LINETHREE) < 1, "No Update",
If(Length($LINEONE) > 1 and Length($LINETWO) < 1 and Length($LINETHREE) < 1, "- " & $LINEONE,
If(Length($LINEONE) > 1 and Length($LINETWO) > 1 and Length($LINETHREE) < 1, "- " & $LINEONE &"\n"& "- " & $LINETWO,
"- " & $LINEONE &"\n"& "- " & $LINETWO &"\n"& "- " & $LINETHREE)))

The request came back that they would only like to see the Date and the Comment (not the User name). I was able to modify the above formula to remove the User name, but only for the top most entry.

I am trying to modify the original formula so that it will return just the date of the comment and the comment. The updated formula (below) does strip out the User name, but only for the most recent entry.

Updated formula:
var text LINEONE = NotLeft(Left([Status Update]," "),1) & ": " & Part([Status Update],2,"]");
var text LINETWO = NotLeft(Left(Part([Status Update],2,"["),9),9) & ": " & Part([Status Update],3,"]");
var text LINETHREE = NotLeft(Left(Part([Status Update],3,"["),9),9) & ": " & Part([Status Update],4,"]");

If(Length($LINEONE) < 3 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "No Update",
If(Length($LINEONE) > 1 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "- " & $LINEONE,
If(Length($LINEONE) > 1 and Length($LINETWO) > 1 and Length($LINETHREE) < 3, "- " & $LINEONE &"\n"& "- " & $LINETWO,
"- " & $LINEONE &"\n"& "- " & $LINETWO &"\n"& "- " & $LINETHREE)))

This successfully pulls out the User name, but only for $LINEONE.

[Status Update] field actual entries:
[AUG-28-24 QuickBase Admin] 7.30.24 New offer received from Diamond Rock Construction - low at $2.2 mil.
[AUG-28-24 QuickBase Admin] 7.11.24 Price renegotiations during feasibility period have failed and Buyer has submitted a rescission agreement. We will go back to market.
[AUG-28-24 QuickBase Admin] 7.3.24 Price reduction request received for $2,212,800. Pushing back to $2,790,000.
[AUG-28-24 QuickBase Admin] 6.10.24 Title Review period has passed. 10 days from receipt of Title was 6/3.
[AUG-28-24 QuickBase Admin] Blackout dates of closing early due to office outages.

Output of [Status Update] after running updated formula:
- AUG-28-24: 7.30.24 New offer received from Diamond Rock Construction - low at $2.2 mil.
[AUG-28-24 QuickBase Admin
- : 7.11.24 Price renegotiations during feasibility period have failed and Buyer has submitted a rescission agreement. We will go back to market.
[AUG-28-24 QuickBase Admin
- : 7.3.24 Price reduction request received for $2,212,800. Pushing back to $2,790,000.
[AUG-28-24 QuickBase Admin

Any suggestions to my formula that would result in the formatting of the 2nd and 3rd most recent entries to match that of the 1st most recent entry?

Thanks

Best answer by EmberKrumwied

Using the wonderful explanation provided by MarkShnier in this post, I was able to answer my own question. Posting full formula in case it helps others.

The formula below is used to return up to the 3 most recent comments in a notes field with log entries turned on. New comments are prepended in the field. If there are no comments, it will return a "No Update" value. I needed the display format to just include the date of the entry and the actual comment. Hope this helps someone else.

Much appreciation to Mark and this forum for assistance in developing this formula.

var text LINEONE = Left(Part([Status Update],2,"["),9) & ":" & Right(Part([Status Update],2,"["),"]");
var text LINETWO = Left(Part([Status Update],3,"["),9) & ":" & Right(Part([Status Update],3,"["),"]");
var text LINETHREE = Left(Part([Status Update],4,"["),9) & ":" & Right(Part([Status Update],4,"["),"]");

//starts by pulling out the date of the comment, then adds a delimitator ":", then pulls out the actual comment

If(Length($LINEONE) < 3 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "- No Update",
If(Length($LINEONE) > 1 and Length($LINETWO) < 3 and Length($LINETHREE) < 3, "- " & $LINEONE,
If(Length($LINEONE) > 1 and Length($LINETWO) > 1 and Length($LINETHREE) < 3, "- " & $LINEONE &"\n"& "- " & $LINETWO,
"- " & $LINEONE &"\n"& "- " & $LINETWO &"\n"& "- " & $LINETHREE)))

//this section determines the "number" of comments in the field, then returns either "No Update", or the most recent comment, or the 2 most recent comments, or the 3 most recent comments.

EmberKrumwied · Accepted Answer