+5Hello all!
I’m trying to create a formula that will extract all text from a string that comes after the last comma in that string.
For example:
From the string “Fashion sketch from A. Beller & Co. of a Lelong Cape, Blouse, and Skirt, circa 1926” I need to extract “circa 1926”
I’m struggling to find a way to point a formula to that last comma and space in the string.
Thanks!
+29Welcome to the Airtable community!
There currently are no Airtable formulas that will search a text string from the end. Thus, unless you know the number of commas that will appear in the text string, you cannot do this with just a formula.
On the other hand, JavaScript has many string handling functions, so you could use a script (Scripting App or Automations Scripting Action) to extract the desired text. If you have a free account, Scripting App will be free until March. If you want to use an Automation Scripting action, you must have a Pro subscription.
There are many ways to write the code, depending on your level of comfort with the JavaScript (or your budget for hiring someone to write it for you), your Airtable subscription type, and how you want the script to run (automatically, per record, or in bulk).
If you want to write the Script yourself, here is some code to get you started. Note this is JavaScript for a Scripting App or Scripting Action, not a formula field.
let extractedText = "";
let lastCommaPosition = originalTextString.lastIndexOf(",");
if (lastCommaPosition >= 0) {
extractedText = originalTextString.slice(lastCommaPosition + 1)
}
If you would like additional assistance setting up a scripting solution, feel free to send me a direct message.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.