Extracting a number from a string

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.

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:

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.

Thanks, I think i’m doing something wrong with it though as its giving me an error message.

I meant for you to do the formula from the other table, but if you’re doing it from this table, then you need to add &"" after each reference to the field.

Add it IMMEDIATELY AFTER the closing curly bracket for BOTH of your field references.

Amazing, that did it, many thanks!

An once again, there are many different ways of doing things. We were all looking a extracting the number from different strings …

• extracting from the linked record field
• extracting from the original record
• extracting from a lookup of the original record

Also, this new screen shot shows more information and an inconsistent data format for the 12 option–it doesn’t have the inches specifier, probably doesn’t have a space, and doesn’t list number of portions. If there is no space, it will mess up the formula that looks for a space.

I still prefer a schema change to get the size in a field by itself, with a formula field to state the portions.

Yeah, people usually use lookups instead of rollups. But that doesn’t mean that lookups are better than rollups. With a rollup, I always know what data type I’m getting at the end (number, string, or array). With a lookup it is hard to be sure. This screen shot is one example why I dislike lookups.