Question

Trying to get the last two words from a rollup containing multiple commas in variable amounts. Need a formula that slice the string before the second last comma, and return everything after that. Currently googling regex trying to find the answer. If someone knows, please answer.

Answer 1

I had a similar problem. You can make the necessary tweaks to my code, if you find it useful.

As a bit of context, I had to slice a string made up of numbers separated by a hyphen, something like this: 2022-10-21. Basically, it's a date, although Notion won't recognize it as such. So, I created 3 formulas, each one to get the three parts of the string separately. I named these shorter strings "1st slice", "2nd slice", and so on.

The name of the string is prop("Name")
The first formula is: replaceAll(prop("Name"), "-.*", "")
The second formula is: replaceAll(if(length(prop("Name")) > length(prop("1st slice")), replaceAll(prop("Name"), concat(prop("1st slice") + "-"), ""), ""), "-.*", "")
The third formula is: if(length(prop("Name")) > length(prop("1st slice")), replaceAll(prop("Name"), concat(prop("1st slice") + "-" + prop("2nd slice") + "-"), ""), "")

After that, you just have to concatenate the strings you obtain. If you need any further help, I will be glad to help you.

Answer 2

Update on the solution to my question above.

As mentioned in a previous reply, realized was trying to solve a constructed problem. Had built a workspace for our team in Notion. In it am using a PARA structure (Projects, Areas, Resources, Archive). Using hierarchical databases to manage tasks from (Areas > Goals > Projects > Tasks > Todos > Time).

In the time database had sorted the data into two categories: Quarter and Month. The core database is Projects as that is where most things are defined, especially the time estimates. Was allowing project owner to select multiple time pages (i.e. multiple quarters or months).

This gave me multiple date ranges in the related column. So in order to calculate the deadline, estimated completion, was using trying to use end() to pick the last date. Obviously, this did not work as the data was a string() not a date().

That's when the question here was posed as was trying to extract the last date of the string which contained multiple date ranges.

Realized this was an overcomplicated solution, and ended up creating time templates consisting of Months, Quarters, and Halfs. Also restricting each project to only one of these. So by default, if the project is longer than 6 months, we split it up into multiple projects.

Not the technical solution expected to end up with, but was much faster to implement and actually works really well in practice.

Hope it helps someone else if they read this.