4E+ 3.999E+ Excel formatting issue

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

Workflow

Datasets

Input

Tips and Tricks

Best Practices

Accepted answers

All comments

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:

2022-02-23 12_28_42-Alteryx Designer x64 - New Workflow1_.png

And then a formula like this:

2022-02-23 12_28_53-Alteryx Designer x64 - New Workflow1_.png

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!

3.9_4E+issue.yxmd

3.9E+ 4e+ issue.bmp

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!

TestSample4E.xlsx

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703