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

[ramedfr]

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.

[ChrisTX]

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]

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]

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]

Sorry, here's the picture.

[patel_bm · Accepted solution]

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]

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.