E+, Scientific Notation, Excel to Alteryx

[sowjanyayinti]

I am attaching a single test row sample file and also posting the snapshot of the issue. I have checked several threads previously posted in this community on the issue but not seeing the results I want so asking for help. How do I get alteryx to give me the full number seen and not just the 1.02E+20?

Here is the catch:  The values in Bnumber column will not always be just numbers. There can be values with just numbers as specified in the example and can also have alpha numeric values like 'ABEF34567890' etc. 

I tried multi formula to convert to fixed decimal, double and all that jazz but nothing worked. Any ideas?

[alexnajm]

This should work: IF Contains([Bnumber], "E+") THEN ToString(tonumber([Bnumber])) ELSE [Bnumber] ENDIF

[sowjanyayinti]

@alexnajm - that just gives 1.0208070225e+20

[alexnajm]

It worked for me for the test values I put in - is this a real value? Can you test it on your actual data?

[sowjanyayinti]

This is a real value and that is why i am having a hard(should I say fun) time with it.

if you look at the picture i posted, in the excel cell you can see the actual value - basically it has 10+ zeroes. However when i convert it into any type, those zeroes are gone and some other number comes up. 

I am just trying to figure out how to get it to give me 102081000000000000000 from 1.02E+20

[alexnajm · Accepted solution]

Gotcha - ToString(ToNumber([Bnumber]),0) worked for me then

[lepome · Accepted solution]

Is it to be used as a number, or is it to be used as an identifier?  If the latter, force it to be text before you import it. 

If you use a blue tool on a long number (even if it's Fixed data type) it gets turned into binary behind the scenes.  You need to change it to a String by directly editing the data type of the field, not with a blue tool.  Or set the data type during the import.

A long time ago, I wrote this article about the phenomenon, but in the years since, all the formatting was removed (https://knowledge.alteryx.com/index/s/article/Big-numbers-and-Alteryx-1583461075214). I took my inspiration from Chris Kingsley's article,  https://knowledge.alteryx.com/index/s/article/Floating-point-numbers-are-surprisingly-strange-158346...