E+, Scientific Notation, Excel to Alteryx

Scientific Number.png

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?

TestScientific.xlsx

Help

Accepted answers

alexnajm

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

lepome

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-1583460191717

All comments

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

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

lepome

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703