How to multiply and add numbers in a cell

[AnnieTacticomba]

Hi Airtable Community,

Looking for some advice. 

I have columns where I have cut length of webbings in a cell.  I have noted the placement of the webbing, cut length and quantity in the cell.  I wanted to have a column that can take the numerical data and divide by 36" (i.e. [(9.75 x 1)+(17x2)]/36)), to give me usage of the material.  Is that possible without having to manually split each cut length into multiple columns?  I do need the text description of the location (i.e. Haul loop) or else I cannot identify what the cut lengths are for, if I need a column for each description of cut length then I will have a lot of columns which I'd like to avoid.  

Any suggestions would be appreciated!

Thanks,

Annie

[TheTimeSavingCo]

> Is that possible without having to manually split each cut length into multiple columns? 
The only way I can think of doing that is with a script I'm afraid

[kuovonne]

Given what you show in your screen shot, it is possible to extract the individual numbers with REGEX formula functions, and then perform the math. The formula functions would be particularly tricky because you use both fractions (3/4) and decimals (86.26) in your text.

However, I highly recommend splitting out the information into multiple fields instead, then use a formula field to combine the information into the pretty looking text that you currently have. In fact, I would take that a step further, and recommend that you use linked records in a new table for this information - one linked record for each location. The other table would have several fields:
- the location
- the length
- the quantity
- a formula field that multiplies the length times the quantity to determine total inches
- a formula field that makes the pretty text for the one location (Haul loop 9.75 x 1)
- the backlink to this table

Then in your original table, have a two rollup fields. One rollup of the total inches for all pieces of webbing with the formula SUM(values). You can then divide the sum by 36 to get your yardage. The second rollup is for the text and would use the formula ARRAYJOIN(values, " ").

On advantage if this system is that you can have multiple pieces of webbing for each record, yet the number of fields stays the same, even if a single record ends up with several pieces of webbing. You also don't have to worry about fragile formula fields that might break if you don't type the information into the text field correctly.

[AnnieTacticomba]

Hi Kuovonne, 

Thank you for this.  This is very helpful. 

I am interested in REGEX, I can consolidate the fractions into decimals.  I am concerned if I create new table and fields it will bump me to paid plan if I add more rows.  But how do I write a REGEX formula to extract the numbers and how do I write the sum formula to multiply and add all numbers within a column together?  For regex, I have space between the parenthesis and between number and multiply sign (Haul loop 9 3/4 X 1) (Molle 17 x 2). The number of locations vary so there could be 2 places to multiply and add or 3 places to multiply and add, etc.  (See print screen)

And for the splitting into fields and roll, is the attached print screen what you are recommending?  And concatenate to combine all into one string in the main table and do the formula to add it all together?  (See print screen)

Your suggestions are much appreciated! 

Annie