Extracting a number from a string

[Harry_Batten]

Hello, how can I extract the 6" and the 8" from these strings?

[kuovonne]

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.

[Harry_Batten]

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

[kuovonne]

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

[Harry_Batten]

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

[ScottWorld]

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]

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?

[ScottWorld]

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

[kuovonne]

Harry_Batten:

Unfortunately it’s not stored as just ‘6”’

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

[ScottWorld]

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