Skip to main content

Trying to break out individual words from a long text field

  • July 23, 2019
  • 4 replies
  • 46 views
M

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!

4 replies

JonathanBowen
Forum|alt.badge.img+18

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
Forum|alt.badge.img+21

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( ...
M
  • Matthew_Pupa
  • Author
  • New Participant
  • July 24, 2019

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

M
  • Matthew_Pupa
  • Author
  • New Participant
  • July 24, 2019

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!

Terms & ConditionsAccessibility statement