Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Conversion of values formatted as Custom (accounting) in Excel to Number

ramedfr
7 - Meteor

Hi all,

 

In an .xlsb file I have several values formatted as Custom using accounting rules (parenthesis for negarive numbers, dash for zero). Since Alteryx seems to interpret it as a String field, I can't use math operations with them. So I tried to convert them to numbers.

 

Using the Formula tool I created a new column with the expression ToNumber([column_name],0,0,","). In the output of that tool all the values under column_name are zero.

 

How can I convert these values to numbers?

 

Thank you.

6 REPLIES 6
ChrisTX
15 - Aurora

In the Input Data tool, try using the file format "Microsoft Excel Legacy".  That driver is the one from Microsoft.

 

The file format "Microsoft Excel" driver is maintained by Alteryx, and I had problems importing Time data with that driver.

 

Chris

patel_bm
8 - Asteroid

Hi @ramedfr 

 

You can read the data from excel as string and use String functions Replace and ToNumber to get the result you want. You may have to copy values in Excel as string in separate column for this.

 

Alternatively, the workflow seem to be able to pick appropriate values reading from excel itself.

 

Both examples are in the attached workflow.

 

ramedfr
7 - Meteor

Hi @patel_bm

 

Through the replace function I could get rid of the parenthesis.

However, the ToNumber function doesn't consider all the digits that come after the first thousands separator, ".".

 

Please find attached a picture of what I get when using the ToNumber function.

 

Thoughts?

 

Thank you.

 

 

 

 

ramedfr
7 - Meteor

Sorry, here's the picture.

patel_bm
8 - Asteroid

Have you tried using replace function to remove thousand separator as well before converting to number?

 

In the end the text should read like 123456.78 before you use ToNumber function. Please make sure you have non integer datatype when applying ToNumber function to retain pose decimal values.

ramedfr
7 - Meteor

This worked. Thank you.

 

Besides using replace to the thousand separators, I needed to create one column to use the replace function and another one to convert the result to number.

 

 

Labels