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

[HW1]

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?

[DataNath · Accepted solution]

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!

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