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

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.

mtm.PNG

custom.PNG

Workflow

Parse

Accepted answers

patel_bm

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.

All comments

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.

NumberConversion.yxmd

ramedfr

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.

Tonumber.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703