Formula Help when Slicing Paragraphs of Text

[Mitchell_Wells]

Hi all,

I want to slice out pieces of a notes column based on its heading. This screenshot really tells the whole story:

Formula from the screenshot:
Slice(thisRow.Notes, Find(Meeting1, thisRow.Notes), Find(Meeting2, thisRow.Notes) - 1)

For Meeting1 and Meeting2 I am using named formulas. Those formulas are as follows:
nth(thisRow.[Discussed in],2)

It’s pulling from a Relation column with the name of the meeting.

I’m nearly there but my result is slightly off. I intended to cut off before @Budget Meeting but for some reason it is continuing on for 20 characters more than I wanted. If change my last number from -1 to -21 instead everything seems to be working just fine? But I don’t understand what’s going on.

Then if I translate this formula over to instead slice between Meeting 2 and Meeting 3 it needs -34 end position and the title is cut off.

Formula from this screenshot:
Slice(thisRow.Notes, Find(Meeting2, thisRow.Notes), Find(Meeting3, thisRow.Notes) - 34)

My only thought is my formula is not reporting honestly the position of each heading, but I don’t know why…

I know this is a pretty tangled mess but if anyone could give some insight I’d appreciate it. Also if there’s a simplified or alternate formula/table structure to accomplish this I’d love to here it.

Thanks!

[Mahmoud]

Hey @Mitchell_Wells .

Great questions! Let’s delve into the details behind the hard-coded numbers and how they produced the desired slice. The reason for changing from -1 to -21 is because there are end of line characters (“\n”) and spaces between the first character “@” of Meeting 2, and the last inner body character in Meeting 1. Likewise, this applies to the number 34.

To overcome this issue, we can employ the Length method to obtain the length of each meeting. Additionally, we can utilize the Trim function to remove any leading or trailing white spaces. By incorporating these adjustments, the formula now becomes:

Slice(
  thisRow.Notes
  Find(Meeting2, thisRow.Notes) +
    Length(Meeting2) +
    1,
  Find(Meeting3, thisRow.Notes) - 1
)
  .Trim()

Hope this helps!

[Mitchell_Wells]

Hey @Mahmoud ,

Thanks so much for giving this a crack. I had to add a comma after the first ThisRow.Notes for the formula to work at all - hopefully that doesn’t change the intention behind it.

I’m running into similar problems with this formula though where it’s missing the cut-off, this time more severely. Here is what it’s grabbing now:

However your suggestion about the end of line characters makes sense and I have created a “step 1” column where I convert my “Notes” column using this formula: Substitute(thisRow.Notes,char(10) ,"")

Then I run these edited notes through the formula you provided and now it works! Thank you for your help.

The only remaining issue is that the Substitute function stripped the formatting of my notes, so now its plain text. But I think this is probably fine for this application.

[Mahmoud]

I’m glad to know that my suggestion allows you to make progress on this task/problem. Happy to help!