Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Extracting a number from a string

5662 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Harry_Batten
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, how can I extract the 6" and the 8" from these strings?
Screenshot 2022-05-22 at 16.29.17

14 Replies 14

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.

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

If there is only one linked record, you can use any of the aggregation formulas that return a number. I usually use MAX(values)

Thanks for your reply, how would I use that formula in this instance?

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.

Harry_Batten
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

@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:

Screen Shot 2022-05-23 at 8.39.59 AM

If you have any questions, let me know!

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+"
)

I wouldn’t recommend REGEX, I would just use the simple text extracting formula that I provided above.