Missed the Q4 Fall Release Product Update? Watch the on-demand webinar for more info on the latest in Designer 24.2, Auto Insights Magic Reports, and more!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to covert a string formula to numerical value

rxing
5 - Atom

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.1
Field2809.50
Field3297.1+5+0+123+34.5-22.3
Field45505.00-100-5

 

And I want output as a numerical value:

 

Field170.7
Field2809.5
Field3437.3
Field45400
3 REPLIES 3
fmvizcaino
17 - Castor
17 - Castor

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
5 - Atom

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:

 

Field110+5
Field22.1
Field3 
Field43+4.5
Field51+2+3+4

 

And the output is below:

Field1105
Field22.1
Field37.5
Field410

 

 

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
17 - Castor
17 - Castor

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

Labels
Top Solution Authors