4E+ 3.999E+ Excel formatting issue

[RadAlt]

Hi All,

We have some clients' accounts which consist of "4E," which causes issues in Excel. It thinks this is a very long number adding 3.999E+ or 4.00E+ etc and we struggle to find a solution within the workflow which would help Alteryx to read these account correctly, For instance, Client A, should be 4E254 whilst Client F: 4E000 (please see some example below). I tried changing the Type but to no avail. I will be very grateful for your ideas & help!

Thanks,

Rad

Client	Client Account
A	3.9999999999999997E+254
B	3.9999999999999997E+254
C	3.9999999999999998E+247
E	4.00E+129
F	4.00E+00

[mbarone]

Is the problem that 4E254 is fine in Excel, and reads into Alteryx as 4.0e+254?  Or does it come into Alteryx just fine (4E254) and goes out to Excel with the bad format?

[TheOC]

hey @RadAlt please can you attach an example workflow showing this issue, so that i can more easily understand and investigate for you? 

Thanks,
TheOC

[mbarone]

A really hacky way to get it perhaps is to read the numbers in and use a Regex tool to parse it out, then a formula tool to bring it back together, taking into account some numbers will have the "E" and some won't.

So you could try this for the Regex tool:

And then a formula like this:

[RadAlt]

hi guys, thanks for looking into it! I tried Regex but sadly it did not work. This seems to be an excel issue (?) and sometimes we get 3.999E+ value, sometimes 4E+, see the screenshot below. I also attach the workflow with the source data, I ll be grateful for your help!

[TheOC]

hi @RadAlt 

Please can you also attach the excel file, or package the workflow?

Thanks,
TheOC

[RadAlt]

sure, pls find the excel attached, big thanks for looking into it! :)

[mbarone]

In your screenshot the "NEW FIELD" seems to be coming out correctly.  When I output NEW FIELD to excel, it also outputs there correctly too.  Not sure I'm understanding the issue at this point.

[mbarone]

Unless of course you're saying the INCOMNING data to Alteryx has the incorrect format.  In which case I'm not sure what can be done if the correct format can't be logically parsed from the incoming data.

[RadAlt]

hi hi, big thanks for trying to help. Yes, this is an excel issue with reading '4E', I hoped Alteryx would fix that somehow, thanks again