Fixing E+11 notation from CSV/Excel

Use ToNumber([Number]) formula like below.

Hope this helps : )

Tonumber.yxmd

Use below formula

IF REGEX_Match(ToString([Number]), "[\d.-E+]+") 
THEN ToString(ToNumber(ToString([Number])))  
ELSE ToString([Number]) ENDIF

Hope this helps : )

Strange it seems to be working for me.

Workflow:

Hope this helps : )

Tonumber (1) (1).yxmd

All comments

Use ToNumber([Number]) formula like below.

Hope this helps : )

Tonumber.yxmd

Thank you for your help, worked perfectly!

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

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

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

Can you provide expected output for the above.

Hope this helps : )

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

Here is the updated workflow for the scenario.

Workflow:

Hope this helps : )

Tonumber (1).yxmd

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

Dont use select and convert it to string. It should still work without it.

@atcodedog05 This is what it looks like...........the grid below is the metadata for the Input Data tool, the formula is set by default. your formula is showing the error.

Use below formula

IF REGEX_Match(ToString([Number]), "[\d.-E+]+") 
THEN ToString(ToNumber(ToString([Number])))  
ELSE ToString([Number]) ENDIF

Hope this helps : )

@atcodedog05 Worked like a charm, I can't thank you enough for your patience and expertise!!

Happy to help : ) @George_Fischetti

Cheers and have a nice day!

@atcodedog05 I found one more issue, but on a different field. For some reason the Imput tool is not recognizing that the Contract Number can have a suffix. The field is just showing numeric values. I've tried variations on the formula you showed here but having no luck. This is what the data should look like....The suffix can be anything from 1-4 alpha characters.

Appreciate any help you can provide. Thanks!!

Should Be Showing As
00003456IV 00003456
231313RB 231313
5464569FT 546469
528698 528698

Strange it seems to be working for me.

Workflow:

Hope this helps : )

Tonumber (1) (1).yxmd

Thank you again, I found my issue. I was using an Auto Field tool to adjust spacing and cleanup white space since my files are very large, was trying not to store quite a large file.

Appreciate your time, quite the learning experience.

@atcodedog05 see attached. I've given you the account field but also the zip code field which is performing similarly. If I click into each individual record I do see the actual account number, but I'm dealing with 39 files and over 21 million records so that method doesn't work.

AcctZipCodeSample.xlsx

Can you provide the expected output for the input.

@atcodedog05 updated as requested.....

AcctZipCodeSample.xlsx

Data seems to loading properly for me.

If its not working only possible solution would change the datatype in excel before loading it into Alteryx.

Hope this helps : )