Skip to main content

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!

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

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( ...

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


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

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( ...

@Justin_Barrett Very useful tip Justin, thanks!

Reply


Terms & Conditions