Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 11, 2019 05:19 PM
I have a string of text that I want to break into two separate strings of text.
Here’s an example:
I want all of the words that precede the “/” to be one string of text. And then I want all of the words that come after the “/” to be a separate string of text. I want to completely eliminate the “/”.
So, to recap, we start with this:
And then we end-up with this:
Apr 04, 2022 04:40 PM
Welcome to the community, @Riddhi_Mehta-Neugeba! :grinning_face_with_big_eyes:
The only thing that the REGEX_EXTRACT()
function can do is extract text. It can’t control how that text is presented. For that matter, there’s nothing that any formula function can do to make the formula output look like those colored “pills” that you see in single- and multiple-select fields.
You didn’t indicate whether this is a one-time need or something that will need to be repeated on a regular basis. If it’s a one-time thing, it’s pretty easy.
If this is a recurring conversion need, you could use an automation. Before setting this up, add a multiple-select field, but create it with no options. Those will be handled automatically by the automation. Assuming for this example that your original field is named {Options}
, I’ll call the multiple-select field {Options Converted}
.
Set the automation up as follows:
{Options}
field.
Notice how the helper text under “Options Converted” says, “Separate multiple options with commas”. By feeding it the name of the selection from the {Options}
field—which contains comma-separated items—it will automatically create new entries based on those items. If the items already exist, it will select them and not add duplicates.
Here’s a demo of how that works. In this case, I’m manually choosing a selection from {Options}
, but the same behavior would work if the records are being created via a form.
Apr 05, 2022 05:01 PM
It worked like a charm. Thank you so much @Justin_Barrett !
Apr 07, 2022 03:40 PM
Hello @kuovonne, can we meet for your services in order to get a solution like this? Thanks
Apr 07, 2022 06:33 PM
Which solution are you interested in? There have been several slightly different solutions in this thread. I also recently posted this YouTube video about a non-scripting method of creating linked records for each line in a long text field.
If you are interested in hiring me to write a script or explore a formula-based system, you can book a meeting. I currently do not have any open slots, but some should open up in a week or so.
Apr 11, 2022 06:14 AM
I will check the video, thanks!
Apr 15, 2022 03:26 AM
I love this example, it’s elegant and leverages Airtable strengths more than it tries to sidestep the limitations of regex formulas.
Speaking of which, anyone struggling with REGEX_MATCH or REGEX_EXTRACT implementations might want to try forgetting either exists and use REGEX_REPLACE instead. It’s by far the most powerful of the trio and offers almost the entire feature set of RE2, the Google-made engine powering Airtable regex formulas (disclaimer: regular expressions rarely scale well).
Jul 17, 2022 09:37 AM
Hello,
This is my current formula:
TRIM(RIGHT(Name, (LEN(Name) - FIND(" ", Name))))
I just want the name of the country please.
Jul 20, 2022 09:29 PM
When trying to work out a REGEX solution, the first thing to look for is a repeatable pattern. In this case, there’s a colon and space immediately before the country name. With that, you can build an expression that finds—but doesn’t extract—that colon-space combo, then extracts everything else after it:
IF(Name, REGEX_EXTRACT(Name, "(?:\\: )(.*)"))
Breakdown…
?:
combination, which means to find what’s in the group, but don’t actually extract it..
token matches any single character, and the *
after it says to match the previous token zero or more times, effectively grabbing everything else to the end of the stringJul 21, 2022 06:56 PM
Thank you Justin! I really wish I had waited for a response. I did it manually *sigh. I’m saving this though!
Jul 21, 2022 07:27 PM
I have another question of IF. Currently I have (DATETIME_FORMAT({Date of donation}, ‘M/D/YYYY’) & “—”) & Donor
But if the field “On behalf of corporation” is checked, I want to be (DATETIME_FORMAT({Date of donation}, ‘M/D/YYYY’) & “—”) & Donor Employer