How to covert a string formula to numerical value

[rxing]

Hi,

  I wish to calculate a string type formula to a numerical value. The string data contains variable amount of numbers and plus and minus symbols like '+' and '-'.

  I have the following expamle string data:

Field1	51.6+31.2-12.1
Field2	809.50
Field3	297.1+5+0+123+34.5-22.3
Field4	5505.00-100-5

And I want output as a numerical value:

Field1	70.7
Field2	809.5
Field3	437.3
Field4	5400

[fmvizcaino · Accepted solution]

Hi @rxing,

Alteryx currently don't have a indirect function as excel has so I've developed a batch macro to help you with that. Take a look

Best,

Fernando Vizcaino

[rxing]

Many thanks to your help!

By the way, I have some empty input and the marco generate error "Record #9:Tool #4:Parse error at char(0):Empty expression ".  The sequence of generated data is also erased. Here is an expamle input:

Field1	10+5
Field2	2.1
Field3
Field4	3+4.5
Field5	1+2+3+4

And the output is below:

Field1	105
Field2	2.1
Field3	7.5
Field4	10

The field3 was deleted,however I want to keep this field as empty. Do you know how to solve this problem?

Best，

Ryan Xing

[fmvizcaino]

Hi @rxing ,

It became a bit more complicated but there it is.

If you want to understand a little more about batch macros https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

Best,

Fernando Vizcaino