How do I use text fields in formulas on my Mac mini Numbers app?

If you want to keep the text fields as is rather than splitting them into columns then it is possible to have a formula extract the numbers from the text and multiply them to fill out the Load in your workout sheet.

If your WSR text is in C3 then the formula (in cell D3 of the example) would be:

=REGEX.EXTRACT(C3,"\d+",1)×REGEX.EXTRACT(C3,"\d+",2)×REGEX.EXTRACT(C3,"\d+",3)

The "\d+" means match one or more numerical digits. The 1,2 or 3 in the second parameter means which occurrence, e.g. 1 for the W, 2 for the S, 3 for the R.

REGEX.EXTRACT - Apple Support

SG

I've found an easier way, using the new TEXTSPLIT function in the most recent version of Numbers.

D3, filled down:

=PRODUCT(TEXTSPLIT(C3,"×")×1)

F3, filled down:

=PRODUCT(TEXTSPLIT(E3,"×")×1)

etc.

TEXTSPLIT splits the text at the delimiter ×.

The ×1 coerces from text to a number that can be multiplied.

You could also use VALUE, e.g.: =PRODUCT(VALUE(TEXTSPLIT(C3,"×")))

PRODUCT multiplies the numbers.

TEXTSPLIT - Apple Support

If you can't upgrade Numbers to the latest version (14.4) then you can use REGEX.EXTRACT as in previous post.

Use ; instead of , in the formulas if your region uses , as the decimal separator.

SG

You can do the opposite. You can put the formula =2*3*4 into a cell and use the FORMULATEXT function in another column to show the formula.

Hi Tom,

Try something like this:

Does that get us closer to your overall aim?

Regards,

Ian.

TomMcIntosh01 wrote:

Have a string like 2*3*5 in one columns and want the product =2*3*5 in a second column. Was having to put 2*3*5 in both columns instead of just the first.

But what are you trying to accomplish? (From the description it doesn't sound like something spreadsheets are designed to do, but it would help to understand your purpose).

SG

I don't think you can do that but, before saying so definitively, could you give an example of the problem you are trying to solve and what you are trying to accomplish?

SG