Converting V_String to Number with exact precision

[adam_rafael_18]

Hi,

I am bringing in a CSV file where I have a column with

• numbers greater than 1,000,000,000
• numbers that are less than .00000001 but greater than 0 (that are being brought in scientific notation)
• nulls

When bringing this in from a csv, It comes in as v_string, and no matter how I try to convert to a number, I am running into an issue, whether it is 

• the large numbers being rounded
• the small numbers being converted to nulls
• the nulls becoming 0

I need everything to be exact, and appear in non-scientific notation. Any recommendations?

Thank you!

[binuacs]

@adam_rafael_18 Can  you share some sample data?

[DataNath]

Hey @adam_rafael_18, does anything in here help? https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Converting-Values-from-Scientific-E...

Specifically the ToNumber() function.

[tandon]

Tried to simulate the issue but can;t see it. please can you share sample data.

[adam_rafael_18 · Accepted solution]

Was able to solve: What I did was first use a multi-row formula to convert to number IF the values were not null. After that, I filtered for values that contained 'E" (anything that contained E needed to be changed, whereas anything that did not was good to go). From there, I used a text to columns with a delimiter of E, and ended with 3 columns (so as an example, I had 3.0000009, E, and -09) I then did a formula where I did 3.0000009 / (Pow(10,-09)). After that, I unioned the filtered data together. Thanks for the help!