How do I correctly parse a column containing numbers and text?

I have a column that has numbers and text as customer number values.

These numbers are read from a .csv file and for some reason that I cannot determine, it reads them as repeated digit numbers

e.g. 1009.007 is read as 1009.0069999999999

I cannot just import them as float as then the text values are force converted to 0

So, to correct for this behaviour, I created a workflow. please find attached.

I might have over engineered the process however, the output for large numbers comes out in exponential terms.

e.g. 20065180 is 2.007829e+07

Is there a better way to treat this so that the integrity of the column is maintained?

Reconciling numbers.yxmd

Workflow

Input

Parse

Best Practices

Accepted answers

DataNath

Hey @HW1 how does this look? As the Customer Number is read in as text, wrapping it in ToNumber() should do the trick. However, as some of them genuinely contain letters, these will be lost during this conversion. Therefore, the formula below just tells Alteryx to leave anything containing a letter as is, and convert everything else to a number:

IF REGEX_CountMatches([Customer Number], '[a-zA-Z]') = 0 then ToNumber([Customer Number]) else [Customer Number] endif

You can also see that this fixes the issue of scientific notification, that Alteryx automatically tries to apply when still in text form:

Have attached the amended workflow. If this doesn't help then let me know!

Reconciling numbers.yxmd

All comments

DataNath

IF REGEX_CountMatches([Customer Number], '[a-zA-Z]') = 0 then ToNumber([Customer Number]) else [Customer Number] endif

You can also see that this fixes the issue of scientific notification, that Alteryx automatically tries to apply when still in text form:

Have attached the amended workflow. If this doesn't help then let me know!

Reconciling numbers.yxmd

HW1

This is perfect!

Thank you!

DataNath

No problem - happy to help! Feel free to mark the solution if this has fixed the issue, or ask any further questions if not!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335