ToNumber Ignoring Text at End of Number

[danielkresina]

Suppose you have a text value of "123AA".  If you try converting it to a number using ToNumber('123AA',1) the result should be 0 since it will try to convert it to a number but fail due to the text that's included.  Instead, I'm finding that it returns 123 and silently ignores the text on the end of the number. This leads to unexpected results.

Interestingly if the number has text at the front instead of the end like "AA123" then it does return 0 as expected.

Attached is an example.

Why is this happening?

[CharlieS]

I suggest applying a RegEx modification to your formula. 

ToNumber(REGEX_Replace([UPC],"[^\d]",""))

The RegEx there will remove any non-digit character before the conversion is made. This should be more reliable for your application. 

[danielkresina]

Thanks I agree RegEx could be an alternative approach, but either way, the scenario I identified above appears to be a bug.  The expectation is that when you use the ToNumber formula, it should throw an error and return 0 any time text is encountered in the value you are trying to convert, right? 

Currently the formula behaves different if the text appears at the beginning vs. the end of the string which is confusing.

Could someone from Alteryx confirm if this strange behavior is intentional or not?  Thanks.

[estherb47 · Accepted solution]

Hi @danielkresina 

The way I understand the ToNumber function working is that it works with strings that begin with numbers, and cuts off when a non-number is encountered. Below are some more examples. So you can see that when a string starts with a number, Alteryx returns that number and every other number until it encounters a non-number (text and a comma in the below examples)


That's the way the function works. 

To get a result along the lines of what you want, try this instead:
IF REGEX_CountMatches([UPC], "[^\d]")>=1
THEN 0
ELSE ToNumber([UPC])
ENDIF

This searches for anything that's not a number in a field, and assigns a 0 if a non-number is found.

Cheers!

Esther

[danielkresina]

Yes, I agree that's how it is working, it just seems counter intuitive to me.  Alteryx needs to clarify the documentation because at this point all they say is that the function  "Converts a string parameter to a number".  They should say "Converts the leading digits in a string into a number" if that is how they intend the function to work.

Personally I think it would be better if the function returned 0 if ANY text was found anywhere in the field since that would indicate to the user that the fields was not able to be successfully converted to a number in it's entirety.

I guess this should go on the Ideas section.

Thanks to all for confirming that I'm not the only one seeing this behavior.

[ChrisTX]

I've sent several emails recently to suggest documentation improvements.

The email address for Documentation Feedback is helpfeedback@alteryx.com

...found at the bottom of this page https://help.alteryx.com/current/Reference/Functions.htm

(and at the bottom of several other pages)