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!
Best answer by Jeremy_Oglesby
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.
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.
+14Hi @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.
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.
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.
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).
+29Don’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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.