Parsing Data that matches

[andy_duong]

Hi,

I am trying to create a formula that will associate tags with a given row if that row contains text that matches entries in a row from another table.

For example, we have a table that contains a set of emails received:

Lisa spoke to a at b company
Kyle spoke to c at d company
Judy spoke to e at f company

Another table contains a list of names such as:

Judy
Kyle
Megan

I want to create a field on the messages table that will show Judy and Kyle as associated tags for that message because the strings Kyle and Judy match the entries in the list of names.

Appreciate any help!

Andy

[Jason · Accepted solution]

Hi @andy_duong,

What is the extent of the list of names in the other table? If it’s a short list, you can write a conditional statement to look out for a specified list of people and return their names in a list. Here’s a formula for that:

IF(
   FIND(
      "Judy",
      Notes
   ),
   "Judy" & ", " & "\n"
) 

& 


IF(
   FIND(
      "Kyle",
      Notes
   ),
   "Kyle" & ", " & "\n"
) 

& 

IF(
   FIND(
      "Lisa",
      Notes
   ),
   "Lisa"
)

If instead you have a larger table with a growing list of names, you may need to turn to the scripting block to write something to account for that.