Trying to break out individual words from a long text field

[Matthew_Pupa]

Hello Airtable community. I’m playing around with the formula field reference, and I’m not finding exactly what I’m looking for. I have a long text field, where I’m collecting messages. I’m basically trying to extract key words out of it and save those words to corresponding columns. For example, for the message “I have a young brown puppy who is a male”, I want to break that out into columns so dog_color would get a value of ‘brown’, dog_gender would get a value of ‘male’, etc. I’m sure there’s a solution using some combination of formulas like FIND, SEARCH, TRIM, etc., but I figured I’d send a message to the community while I try figuring it out myself. Thanks!

[JonathanBowen]

Hi @Matthew_Pupa - how about this nested IF statement:

IF(
  FIND('black', Text) > 0, 
  'black',
  IF(
    FIND('white', Text) > 0, 
    'white',
    IF(
      FIND('brown', Text) > 0, 
      'brown'
    )
  )
) 

However, this doesn’t allow for more than one colour in the text:

if will just report the first found colour

JB

[Justin_Barrett]

To add to that, you might consider wrapping the text field reference in LOWER(), in case someone capitalizes a word you’re looking for. For example, if someone writes:

Black dog, male, 4 yrs old

then “Black” won’t be found if you’re searching for “black” because Airtable’s FIND() is case sensitive. Using the beginning of @JonathanBowen’s formula as an example, the modification would look something like this:

IF(
  FIND('black', LOWER(Text)) > 0, 
  'black',
  IF( ...

[Matthew_Pupa]

Thanks @JonathanBowen. This is a start! Yes, the case where I have more than 1 color, or two dogs in a sentence is still an issue, but I can save that for a next step. Much appreciated!

Matt

[Matthew_Pupa]

@Justin_Barrett Very useful tip Justin, thanks!