Help

Can I Extract the first sentence of a description and copy it in a different column?

Topic Labels: Formulas
1894 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Brandon_Hatcher
5 - Automation Enthusiast
5 - Automation Enthusiast

HI,
I am using airtable to plan out an event.

I will be creating an app from the airtable data. On the schedule for the app, I want to have a short description that is one sentence long, then when you click on the details of a schedule item I want to see the full description.

Ideally I would like to write the full description out and have the first sentence of the full description be copied into another cell to serve as the short description.

For example:

This would be the long description:
Come to the Ice Cream Social to get to know your fellow attendees. The Ice Cream Social will be held in the blue building next to Hospitality. It is sponsored by ACME Products inc.

This would be the short description (first sentence of above):
Come to the Ice Cream Social to get to know your fellow attendees.

I guess what I am looking for is a formula that would take everything up to the first period on the long description then copy that sentence into another cell.

4 Replies 4
augmented
10 - Mercury
10 - Mercury

Hi Brandon. I’m not great with regex, but you could create a formula field that uses your long description to pull the first sentence.

REGEX_EXTRACT({Long Description},'^([^.!]*)')

It matches everything up to the first punctuation, in this case either . or !. The punctuation won’t be there, however. Couldn’t figure out that bit. If it’s important, you can always add a . using concat in your formula field.

& '.'

Assuming you have two columns: Long Text (Long Text) and Short Text (we will make this a formula soon)
Create a new Formula Column (lets call it formulaFind)
Use this formula: FIND(".",{Long Text})

Capture

Now change the Short Text field to a Formula type field and insert this formula: LEFT({Long Text},{formulaFind})

Capture

Then you should get what you are looking for:

Capture

EDIT

Alternatively you can do this all in one field. Skip the first step creating the fomulaFind column and just add this to the Short Text formula: LEFT({Long Text},FIND(".",{Long Text}))

Capture

You just add another . to the regular expression (i.e.
REGEX_EXTRACT({Long Description},'[^.!]*.')
), it matches any single character – if you’re matching everything up to the punctuation, the next character must be the punctuation.

You can play around with the regular expression here (for example, adding a question mark):

While you can do this, I do not recommend i

  • If you have a sentence that includes an abbreviation, such as “Mr.” the excerpt will end too soon.
  • If you have a very long first sentence, the excerpt may be much longer than what you want.
  • If your opening sentence ends with different punctuation (! or ?) or no punctuation at all (just line break), you will not et the excerpt you want

Here are some alternatives

  • Store your first sentence and main description in two different fields. Then use a formula field to combine them.
  • Create an excerpt that does not have to be a complete sentence. I have written formulas that create excerpts based on character length while also ensuring that the excerpt ends at a word boundary (not mid-word). I am away from my computer, so I cannot look up the formula right now.

I like to use a combination of fields. A manual excerpt field, and a formula excerpt field. If there is a value in the manual excerpt field, the formula shows that excerpt. Otherwise, the formula creates an excerpt from the main text as described above.