Split function in Formula Tool or any equivalent - multiply values from text

[gregx]

Hello,

I have values in text field for example:

"1#2#3.3#-4.9"

I would like to multiply all values separated by #, so in this case it would be 1*2*3.3*(-4.9).

Is there any function that can handle it?

Just to add - Text to Column won't work as the number of items in the text may vary (may be 1, may be 20, there is no rule).

[Christina_H]

There isn't a simple formula for this, although I think you might be able to use Dynamic Formula from CReW macros.  Here's a way to do it.

It does rely on # being the only symbol included that can't be converted to a number.

[binuacs]

@gregx One way of doing this

[Christina_H]

Actually, it looks like a simpler way to do this is on the way.  This suggestion was recently accepted: Add Product to Summarize tool - Alteryx Community

[suby]

Hi Christina, 

Could you please explain the expression in multi row formula on what it does?

[suby]

HI Binuacs,

Could you please explain the expression in multi row formula on what it does?

[Christina_H]

The expression is taking the Product from the previous row (grouped by ID in case you have more than one expression), and multiplying it by the value on the current row.  If the previous row has no value (first row for that expression) it substitutes 1 so that the product will still work.  It's calculating the equivalent of a running sum but with multiplication, so when you get to the final row all values have been multiplied together.