May 15, 2017 02:35 PM
Is there a single function that can extract whole words (either trailed by a space or the last word) from text fields?
We’re scheduling and tracking social media campaigns and have post text including hashtags in a single text field in Airtable. This makes the workflow easy for planning and reporting.
Nearest way I can think of achieving this is a combination of text functions (FIND for the start character “#”, another FIND for the first space character occurrence afterwards, and the MID to extract with the number of characters set to the difference between the “#” and space characters) described at https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference
Is there a simpler way of achieving word extraction? Or recursive word extraction?
May 15, 2017 11:34 PM
I don’t think there is another way to achieve this using the default functions.
An alternative would be a custom API solution or something like Zapier.
May 16, 2017 07:19 AM
Hey @Tuur I found a way! :raised_hands:
To extract the first hashtag word, use the following function madness and replace {Post Text} with the name of your field.
IF(AND(FIND("#",{Post Text}),FIND(" “,{Post Text},(FIND(”#",{Post Text})))),
MID({Post Text},(FIND("#",{Post Text})),(FIND(" “,{Post Text},(FIND(”#",{Post Text})))-(FIND("#",{Post Text})))),
IF(FIND("#",{Post Text}),
MID({Post Text},(FIND("#",{Post Text})),(LEN({Post Text})+1-FIND("#",{Post Text}))),
BLANK()))
This handles:
I can’t imagine what function madness we’d need to try extracting multiple hashtags!
May 16, 2017 07:39 AM
Good job! Multiples are difficult with the default functions because you can’t use repeating or recursive logic (unless there is some predefined maximum of course).
By the way, in most cases I prefer multiple hidden (helper) fields with smaller formulas instead of one big formula, so other people can sort of follow it too. :winking_face:
May 23, 2017 07:05 PM
Agree on hidden helper fields to make it easier to follow. In Airtable, if new fields added to a table weren’t added to all views, I’d take this approach more often.
As well as First hashtag, I’ve used a similar approach for a First @mention
formula field.
IF(AND(FIND("@",{Post Text}),FIND(" ",{Post Text},(FIND("@",{Post Text})))),
SUBSTITUTE(MID({Post Text},(FIND("@",{Post Text})),(FIND(" ",{Post Text},(FIND("@",{Post Text})))-(FIND("@",{Post Text})))),":",""),
IF(FIND("@",{Post Text}),
SUBSTITUTE(MID({Post Text},(FIND("@",{Post Text})),(LEN({Post Text})+1-FIND("@",{Post Text}))),":",""),
BLANK()))
In addition to the same 3 situations handled in the First hashtag extraction, the SUBSTITUTE() function removes any trailing “:” character.