ToNumber Ignoring Text at End of Number

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?

ToNumber_Example.yxmd

Parse

Expression

Accepted answers

estherb47

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

All comments

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.

20190312-ToNumber_Example.yxmd

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703