Formula to trim words

[Alba_Machado]

Hi AirTable friends,

I have records that each have a long text field which is TOO long. That long text field is called “Excerpt.” What I’d like to do is create a formula field called “Trimmed Excerpt” that copies the content from “Excerpt” field but limits the word count to, say, 50. What’s the formula for that? I Googled for a solution but came away empty-handed. Thanks to those of you who take a crack at this one!

[Jeremy_Oglesby · Accepted solution]

Hi @Alba_Machado,

So I think if you are wanting this to be pretty simple, you could just use the LEFT() function. It isn’t able to parse a number of words, but it can parse a number of characters… so here’s a really simple and dumb implementation, where if we assume the average word length is 5 letters plus a space (so 6 characters total), we can extract the first 50*6 characters = 300 characters:

LEFT({Excerpt}, 300) & '...'

(Added the ellipses at the end to signify this is a snippet of a larger text)

If you are wanting something smarter than that which can parse an exact number of words, you are probably moving into Scripting app territory.

[Alba_Machado2]

Thanks, Jeremy! I think cutting according to characters rather than words would work for us, only it would be cool if the final word wasn’t cut off.

[Jeremy_Oglesby]

Ya, I’m pretty sure if you want it to be that smart, you’ll have to move outside of formulas and into Scripting. Not sure if 1) you are on a Pro plan and have persistent access to scripting, and 2) if you actually want to dive that deep…

But if you do want to explore that, it’s definitely possible with a Script (either Scripting App or an Automation with a Scripting Action).

[kuovonne]

Don’t underestimate the power of a formula field!

IF(
  LEN({Long text}) > 250,
  LEFT(
    {Long text}, 
    FIND(" ", {Long text}, 240)
  )  & "...",
  {Long text}
)

You can see the formula, an explanation, and a working demo on my website.

I actually use a souped up version of this formula field to create the excerpts on all the posts on my website.