This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
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.