Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 22, 2022 08:35 AM
Hello, how can I extract the 6" and the 8" from these strings?
May 22, 2022 08:48 AM
Welcome to the Airtable community!
There are various ways to extract the number from the string. However, you are better off using a rollup field to get the number from the source record versus parsing it from the string.
May 22, 2022 08:58 AM
Hi Kuovonne, thanks fro your response. If I use the roll up function, not requires “Aggregation formula which rolls up the values in each linked record” What should I use for that?
Thanks
May 22, 2022 11:31 AM
If there is only one linked record, you can use any of the aggregation formulas that return a number. I usually use MAX(values)
May 23, 2022 03:28 AM
Thanks for your reply, how would I use that formula in this instance?
May 23, 2022 05:12 AM
Hi @Harry_Batten, you won’t be able to use a Rollup to extract the inches from those strings, unless the inches are already stored in their own column in the other table. In which case, most people use lookups instead of rollups.
How is your data stored in the linked table? Are the inches stored in their own column?
If the inches are not stored in their own column, you would need to create some sort of a parsing formula — possibly using REGEX — to pull the inches out of that string.
May 23, 2022 05:30 AM
It’s stored in another table as (for example) ‘6” - approximately 6-10 portions’
Unfortunately it’s not stored as just ‘6”’ tbh I don’t need the “ if it helps, just the number.
How would a regex function work?
May 23, 2022 05:37 AM
@Harry_Batten You wouldn’t need REGEX for that… you would just need to take all the characters to the left of the first space like this:
LEFT({Your text field},FIND(" ",{Your text field})-1)
Put that formula in the other table, and then just lookup that field in the first table.
Take a look at the screenshot below to see how this formula will extract what you need:
If you have any questions, let me know!
May 23, 2022 05:38 AM
Is it possible to store the individual bits of information about each cake in separate fields and then use a formula field to create the primary field?
For example, you could store just the size 6 in a number field and then use a SWITCH
formula field to indicate the number of portions.
That would really be better than extracting the number of inches using a REGEX. Also, in your screen capture, all of the cakes are 6". Are the only possibilities 6" or 8"? Will there ever be different sizes or cakes that don’t have a size? REGEX expressions are notoriously difficult and can be finicky if you don’t know all the possibilities.
This one might work.
REGEX_EXTRACT(
{Order Line Items},
"\d+"
)
May 23, 2022 05:47 AM
I wouldn’t recommend REGEX, I would just use the simple text extracting formula that I provided above.