Fixing E+11 notation from CSV/Excel

[George_Fischetti]

Hi folks, I'm using csv files as my source, there is 40 of them.  when I process each file I have my bank account number showing values such as the figure below

At the source, this field is coming in as a V_WString, I then follow with a Data Cleaning tool and Select tool.  It looks like the format changes to V_String after the Select tool.

How would I fix this so that the entire account number is shown?

Thanks!  George

[atcodedog05 · Accepted solution]

Hi @George_Fischetti 

Use ToNumber([Number]) formula like below.

Hope this helps : )

[George_Fischetti]

Thank you for your help, worked perfectly!

[George_Fischetti]

@atcodedog05 I need to take this one step further, apparently I did not know that there were also alpha characters in this field, the test file I used probably didn't have this condition.   What function could I use to fix both of these situations??  Thanks!!

[atcodedog05]

Hi @George_Fischetti 

Its not clear. Can you provide some sample input and expected output it will help us get a better understanding of the usecase.

[George_Fischetti]

@atcodedog05     Sure....something like this, Alpha characters appearing anywhere in the account number.

[atcodedog05]

Hi @George_Fischetti 

Can you provide expected output for the above.

Hope this helps : )

[George_Fischetti]

@atcodedog05 The expected output is what I've shown above, the notation I'm seeing in the field with the xxxxxxxxxE+11 type format is showing up so that prevents me from seeing the entire account number.   

I want the field to end up being a string field that I can query against.   I'm trying not to go into each file and change the field type manually which I know would correct the issue. I'm also new to Alteryx and trying to solve with some functions to enhance learning.

Thanks!!!

[atcodedog05]

Hi @George_Fischetti 

Here is the updated workflow for the scenario.

Workflow:

Hope this helps : )

[George_Fischetti]

@atcodedog05  Something is still not right - see error messages.    My data coming in from the source is shown below.  The Input Data tool is brining this field in as Double.  I'm using a select tool to change the type to V_String which is generating the error messages shown.  When I use the formula you supplied I'm getting the Parse error; tried to apply a string operator to numeric value.   I think I've totally confused the situation.....