We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Fixing E+11 notation from CSV/Excel

George_Fischetti
8 - Asteroid

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

 

George_Fischetti_0-1634145236918.png

 

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

 

17 REPLIES 17
atcodedog05
20 - Arcturus

Hi @George_Fischetti 

 

Use ToNumber([Number]) formula like below.

 

atcodedog05_0-1634146092671.png

 

Hope this helps : )

 

George_Fischetti
8 - Asteroid

Thank you for your help, worked perfectly!

George_Fischetti
8 - Asteroid

@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
20 - Arcturus

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
8 - Asteroid

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

 

George_Fischetti_0-1634211403487.png

 

atcodedog05
20 - Arcturus

Hi @George_Fischetti 

 

Can you provide expected output for the above.

 

Hope this helps : )

George_Fischetti
8 - Asteroid

@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
20 - Arcturus

Hi @George_Fischetti 

 

Here is the updated workflow for the scenario.

 

Workflow:

atcodedog05_0-1634213507124.png

 

Hope this helps : )

George_Fischetti
8 - Asteroid

@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.....

 

George_Fischetti_0-1634216199522.png

George_Fischetti_1-1634216253674.png

 

 

Labels