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: Field151.6+31.2-12.1Field2809.50Field3297.1+5+0+123+34.5-22.3Field45505.00-100-5 And I want output as a numerical value: Field170.7Field2809.5Field3437.3Field45400

How to covert a string formula to numerical value

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

Workflow

Data Investigation

Accepted answers

fmvizcaino

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

2020-06-04 How to covert a string formula to numerical value.yxzp

All comments

fmvizcaino

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

2020-06-04 How to covert a string formula to numerical value.yxzp

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335