Find multiple values (CSV column) in text string

1

I’ve tried utilizing split(",") and formulamap() since I believe that has to be the key but I may not fully understand the proper syntax.

I want Row 1 to produce Apples and Bananas

Help is much appreciated!

Screenshot 2022-05-29 4.28.41 PM
Screenshot 2022-05-29 4.28.41 PM1361×1080 86.5 KB

2

Hey Scott!

You should split your “name” column on a blank space

  • split(“ “)

That will turn your string of words into a list where each value is a distinct word.

Then you would run a combination of in() and filter to pull out apples and bananas.

Im not at my computer but guessing something like this would work:

  • name.split(“ “).filter(currentValue.lower().in(ingredients.triggerWords))

And if your triggerWords column is just a string value you will need to first turn IT into a list with

  • split(“,”)

And then use that new column as the argument for your in() formula above.

I actually just wrote a whole document on the proper use of split(). Happy to share when I’m back at my computer!

2 Likes
3

Thanks for the prompt response!

In my example, I forgot to include that some of my trigger words are “trigger phrases” which include spaces so it’s problematic to split(" ") . Considering this, what do you recommend?

I look forward to reading your document on split!

4

Oooh interesting!

Yeah, it gets a bit more complicated there. Can you share an updated example/dummy doc with a example words and phrases and I’ll take a look at it?

1 Like
5

So you want to find exact matches on either of the trigger words, correct?

  1. Splitting Trigger Words by a comma is inevitable.
  2. Searching by .Find() in the full text is inevitable too.
  3. The rest is an iterative algorithm to test all triggers from all ingredients:
Ingredients.Filter(
  CurrentValue.[Trigger Words].Split(",").Filter(
    thisRow.Name.Lower().Find(CurrentValue) != -1
  ).IsNotBlank()
)

For significantly better perf you should:

  • make Name.Lower() a separate column in Stories and use that one in the innermost Filter. This will avoid recalculating .Lower() on each check.
  • make Trigger Words.Split(",") a separate column on Ingredients as well. Again, will avoid splitting the value over and over on each change anywhere.

For inexact matching there’s some regex magic but that’s beyond this question.

P.S.

3 Likes
6

Thanks! Works beautifully!

I appreciate the perf tips even more than the solve!

7

Paul for the win! Got to it before I could

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.