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.
- Duplicate your field, making sure to choose “Duplicate Cells”
- Change the field type of the duplicate field to single line text.
- Change the field type to multiple-select. Airtable will automatically create new items based on the comma separation in the text.
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:
- Trigger: When a record is updated. Specifically, only look for changes in the
{Options} field.
- The only action you’ll need is an “Update record” action. Update the triggering record to copy the name of the selected “Options” choice into the “Options Converted” 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.
It worked like a charm. Thank you so much @Justin_Barrett !
Welcome to the Airtable community!
Airtable does not have a split function. You can look into this thread for one method. However, since your end goal is more linked records, and not the actual contents of the different fields, you may want to look into having a script to do all of the processing from splitting the text string to making the linked records.
Hello @kuovonne, can we meet for your services in order to get a solution like this? Thanks
Hello @kuovonne, can we meet for your services in order to get a solution like this? Thanks
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.
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.
I will check the video, thanks!
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.
- Duplicate your field, making sure to choose “Duplicate Cells”
- Change the field type of the duplicate field to single line text.
- Change the field type to multiple-select. Airtable will automatically create new items based on the comma separation in the text.
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:
- Trigger: When a record is updated. Specifically, only look for changes in the
{Options} field.
- The only action you’ll need is an “Update record” action. Update the triggering record to copy the name of the selected “Options” choice into the “Options Converted” 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.
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).
Hello,
This is my current formula:
TRIM(RIGHT(Name, (LEN(Name) - FIND(" ", Name))))
I just want the name of the country please.
Hello,
This is my current formula:
TRIM(RIGHT(Name, (LEN(Name) - FIND(" ", Name))))
I just want the name of the country please.
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…
Parentheses define groups. In this case, we have two groups defined slightly differently.
- In the first group, we begin with the
?: combination, which means to find what’s in the group, but don’t actually extract it.
- In that first group we’re looking for a colon-space combo. However, the colon is a special token in REGEX, so it must be escaped by putting a backslash before it. And because the backslash is also an escape character for strings and we need a backslash as an actual part of the expression string, we must escape that backslash with another backslash.
- In the second group, the
. 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 string
Thank you Justin! I really wish I had waited for a response. I did it manually *sigh. I’m saving this though!
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
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
First off, there’s no need to wrap the DATETIME_FORMAT() function in parentheses to separate it from later pieces that you’re adding. They can all be added sequentially like this:
DATETIME_FORMAT({Date of donation}, 'M/D/YYYY') & "—" & Donor
Extra parentheses groups are really only necessary if some part of the formula needs to be calculated separately; e.g. to control the order of operations with a numerical calculation.
To your question, I would wrap the IF() condition check just around the tail end of that formula, like this:
DATETIME_FORMAT({Date of donation}, 'M/D/YYYY') & "—" & IF({On behalf of corporation}, {Donor Employer}, Donor)
First off, there’s no need to wrap the DATETIME_FORMAT() function in parentheses to separate it from later pieces that you’re adding. They can all be added sequentially like this:
DATETIME_FORMAT({Date of donation}, 'M/D/YYYY') & "—" & Donor
Extra parentheses groups are really only necessary if some part of the formula needs to be calculated separately; e.g. to control the order of operations with a numerical calculation.
To your question, I would wrap the IF() condition check just around the tail end of that formula, like this:
DATETIME_FORMAT({Date of donation}, 'M/D/YYYY') & "—" & IF({On behalf of corporation}, {Donor Employer}, Donor)
Thank you very much for teaching me! I appreciate it!
Thanks for all the help! I am seeing some great solutions, but they seem to be jumping around with type of formula. How do you decide which formula to use?
We want to separate name: Last, First into two columns - first and last
We also want to separate terms for higher ed. From “Spring 2022” into two columns - Spring and 2022.
Thanks for all the help! I am seeing some great solutions, but they seem to be jumping around with type of formula. How do you decide which formula to use?
We want to separate name: Last, First into two columns - first and last
We also want to separate terms for higher ed. From “Spring 2022” into two columns - Spring and 2022.
Welcome to the Airtable community!
There are many ways to do things in code, and complex formulas are code. When deciding which formula to use, it is often a matter of taste/style. Use whichever formula gives the result you want and makes sense to you. The better you understand a formula, the easier it will be for you to maintain it in case you ever need changes. And it is okay to change formulas as you needs and knowledge grow.
Hello! I see that you are very helpful in this forum!
I am trying to solve something in reverse of all of the requests here.
I have dimensions for packages, stored in: {Length}, {Width}, {Height}, {Weight}.
I would like a formula to concatenate them with TABs in between them, like this: {Length} TAB {Width} TAB {Height} TAB TAB TAB {Weight}.
I first tried to string concatenate it but TAB es read just like a SPACE. Then I tried to create an array, but it seems that the formula field doesn’t accept arrays.
The use that I want to give this is one field that could be turned into a barcode or just copied and pasted to a warehouse management system.
Thanks in advance!
Hi All
I’ve scoured this thread and I am still struggling.
I have a spreadsheet which lists. analytics tools different websites.
In a single row, I have a record like:
Google Analytics ; Facebook Pixel (2.9.83) ; Hotjar ; Google Ads Conversion Tracking ; Cloudflare Browser Insights
I’d like each tool to be a separate entry in a single field. I am thinking a ‘multiple select’ field type.
Any advice would be greatly appreciated.
Thanks
Paul
Hello! I see that you are very helpful in this forum!
I am trying to solve something in reverse of all of the requests here.
I have dimensions for packages, stored in: {Length}, {Width}, {Height}, {Weight}.
I would like a formula to concatenate them with TABs in between them, like this: {Length} TAB {Width} TAB {Height} TAB TAB TAB {Weight}.
I first tried to string concatenate it but TAB es read just like a SPACE. Then I tried to create an array, but it seems that the formula field doesn’t accept arrays.
The use that I want to give this is one field that could be turned into a barcode or just copied and pasted to a warehouse management system.
Thanks in advance!
Correct. First off, all whitespace—including spaces and Tab characters—is condensed when displayed in a web browser (unless you insert a non-breaking space via HTML, but that’s another story). Another issue is that the Tab character in a browser doesn’t inherently represent a specific gap size, unlike the Tab behavior on a typewriter (where the concept came from) where the carriage would advance to predefined tab stops. Without some definition of how far to advance when inserting a Tab, the browser just treats it like a single space.
Unfortunately there really isn’t a good way to do the kind of formatting that you’re looking for inside a field. While extra spaces/Tabs will be stored internally, they’re all mashed together for display unless you’re looking at the expanded view of a single field (press SHIFT + SPACE with any single field selected to expand just that field) where Airtable actually makes the effort to ensure that the field data displays precisely as stored.
In short, Airtable fields are great for storing data, but not necessarily the best for designing spacing-dependent layouts for that data.
Correct again. While Airtable’s formula system does contain functions for processing arrays, those only work with data from lookup and rollup fields, the only field types that produce arrays. There’s no way currently to create an array from scratch. Even if there were, an array wouldn’t really help in this instance.
I’m not seeing how those four values would translate into a barcode of any kind by just laying them out with specific space between them. I’m also not sure how that spacing would help when copying the data elsewhere. Could you go into more detail about your desired use case?
Hello @Justin_Barrett , thank you very much for your response.
A little bit of background here: I want warehouse personnel to be able to register entering cargo to the warehouse from their phones with the airtable app.
At the moment, we use a WMS software that looks like this:
This software (Magaya) doesn’t integrate with many other softwares. We use a dimensioner (a scale with depth cameras to calculate the volume and weight of the package). This dimensioner hasn’t got an interface with Magaya, because Magaya has its own; so they created a barcode that includes TABs, so that when it is scanned, the dimensions and weight are filled in Magaya automatically.
We are exploring alternatives to Magaya because of its lack of integration possibilities and adaptation to new ideas. What I was thinking was using Airtable to replace this initial process, however we still need to fill the info in Magaya at the moment. To reduce the extra steps in this process, I was thinking about using the same procedure that the dimensioner uses: the Barcode (or even a copy/paste)
Hello @Justin_Barrett , thank you very much for your response.
A little bit of background here: I want warehouse personnel to be able to register entering cargo to the warehouse from their phones with the airtable app.
At the moment, we use a WMS software that looks like this:
This software (Magaya) doesn’t integrate with many other softwares. We use a dimensioner (a scale with depth cameras to calculate the volume and weight of the package). This dimensioner hasn’t got an interface with Magaya, because Magaya has its own; so they created a barcode that includes TABs, so that when it is scanned, the dimensions and weight are filled in Magaya automatically.
We are exploring alternatives to Magaya because of its lack of integration possibilities and adaptation to new ideas. What I was thinking was using Airtable to replace this initial process, however we still need to fill the info in Magaya at the moment. To reduce the extra steps in this process, I was thinking about using the same procedure that the dimensioner uses: the Barcode (or even a copy/paste)
Thanks for the clarification. That’s definitely a unique use case you’re working with!
Without knowing the inner workings of the barcode system, I don’t think that it will be possible to replicate that. However, it should be possible to pull this off using Typinator, which is a really handy tool that lets you define your own shortcodes that auto-expand into longer chunks of text, including things like tabs and other control characters. Here’s how I’m envisioning its use in this case:
- You use a formula to concatenate the values that you want to insert into those fields, separating them with a single space between them
- In Typinator, define an expansion that uses JavaScript to extract the pieces from the copied text, then output them individually with the proper number of tabs between them.
To use this, the user would copy the formula output from an Airtable field, click in the first field (length) in the Magaya dialog, then enter the defined shortcode. This would trigger Typinator to parse the copied text and insert the values into the proper fields.
I ran a quick test on this and it worked well, though it was a little tricky to set up. If you’d like help with this, message me directly and we can discuss the options.
Thank you very much @Justin_Barrett!
Justin you are amazing, I hope you can help - I've read through these and whilst similar, I could not find a solution to my issue. I have a long string of text, and want to pull out a particular part;
EMAIL
Hey Bob, How's things?
Thank you for your email.
This is the text I want.
Please let me know if I can help. Blah blah blah
blahblahblahblahblahbla
Blahblahblahblah
So there will be lots (random) of lines of text, but I want to pull out the text which will always be sandwiched between Thank you for your email. and Please let me know if I can help.
Is this doable?
I've no idea what I'm doing, nor understand how regex works, but tried this ... and got an error
TRIM(REGEX_EXTRACT({EMAIL}, "(?:.*Thank you for your email. )(.*)(?:\\sPlease let me know if I can help.*)"))
Many thanks, Sarah
@Justin_Barrett Wow, thank you for all this! I've been able to get pretty far with my current conundrum just in reading this thread. My remaining question is: What would the formula be if I want to have something like "Nancy & Lindsey" ported into the first name field, when the full name field reads "Nancy & Lindsey Smith"? Thanks for your help!
@Justin_Barrett Wow, thank you for all this! I've been able to get pretty far with my current conundrum just in reading this thread. My remaining question is: What would the formula be if I want to have something like "Nancy & Lindsey" ported into the first name field, when the full name field reads "Nancy & Lindsey Smith"? Thanks for your help!
Also curious if there's a formula that will shunt over a first name from full name column, if there's just a first name available? Right now it's putting the first name in the last name column because there are no spaces when there's just one name in the full name field. Ay!
@Julia_Barry I'm glad that my earlier solutions were useful for you! I'm sorry that I haven't responded sooner to your follow-up questions. I don't visit the forums any more for a variety of reasons, and the account where I receive email notices of private messages isn't used nearly as often as it used to be.
Re: your questions, at first glance they feel like they're at odds with each other. The first solution that comes to mind to solve the first problem would not work for the second situation, and vice versa. That said, I was able to come up with formulas for both first and last name fields that would work in a variety of use cases:
Here's the First Name formula:
IF(
{Full Name},
IF(
FIND("&", {Full Name}),
REGEX_REPLACE({Full Name}, " [^ ]*$", ""),
REGEX_EXTRACT({Full Name}, "^[^ ]*")
)
)...and the Last Name formula:
IF(
{Full Name},
IF(
REGEX_MATCH({Full Name}, " |&"),
REGEX_EXTRACT({Full Name}, "[^ ]*$")
)
)
