Jul 22, 2019 09:17 PM
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!
Jul 22, 2019 11:52 PM
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
Jul 23, 2019 08:58 AM
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( ...
Jul 23, 2019 05:02 PM
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!
Jul 23, 2019 05:04 PM