How to properly read some values from Excel

Question

Hello everyone!

My Excel file has some values displayed like (0,00) and (990.254,10), for example.
The actual values are -0,0058974562361 and -990254,1013
Alteryx is reading the values as (0,00) and (990.254,10), and as Strings.

I removed "(" and ")", but I can't handle losing digit information.

Any ideas how to handle this?

Thanks a lot!

apathetichell · Answer

is your localization setting is on period as a decimal delimiter? Change this to comma. re-run. You may not have needed to convert at all - you may just have needed to swatch your localization setting.

if you are still seeing this make sure you are converting to something like fixed decimal 20:10 - not fixed:decimal xxx.2

jpscoralick · Answer

Hello @apathetichell

Your solution works almost perfectly.

I need to get all the numbers after the comma.
So (0,00) needs to be -0,0058974562361
(990.254,10) may become -990254,1013
And so on.

Thanks a lot!

apathetichell · Answer

Alteryx reads Excel numbers formatted in (xxxx.xx) as numbers. Double check your source file to see how they are being formatted in Excel.

Having said that - use a multi-field formula. select your columns. turn off the "new" field part - and change datatype to double. use this formula:

if regex_match([_CurrentField_],"$.*$") then -1*tonumber(regex_replace([_CurrentField_],"[]","")) else tonumber([_CurrentField_]) endif