isnumber(v) isn't recognising a number

[GaryKennedy]

Hi Alteryx Community,

I am new to Alteryx so apologies if my question is basic.

I want column named "Replacement" to populate with True if there is a number in field "replacement order#" and to populate with False if a letter is in column "replacement order#"

I tried ISnumber[replacement order#] but it is showing 0 for all entries.

One of the numbers I am trying to return as True is is "40116486, 142698960".

I appreciate any help on this.

Regards,

Gary

[john_miller9]

@GaryKennedy

If the field in question has a mix of letters and numbers it's likely formatted as a String or V_String type.  You might be better off using a regular expression to parse this out to see if it contains a number.

jm

[GaryKennedy]

Yes the column is a V String type. I am not familiar with regex, can you provide some more detail as to how to parse this out please?

[MarqueeCrew]

Regex_countmatches("[^0-9]")=0

[GaryKennedy]

I'm not sure if I did that correctly, sorry I am a complete beginner. 

I am aware that the "replacement order" column contains numbers, how do I return "True" in the separate "replacement" column and "False" if there are letters showing?

When I try the Isnumber after the regex parse I am getting a Parse error at char(30): parse expression#1) on the isnumber formula field.

[john_miller9]

Add a formula tool after your input and enter the expression that @MarqueeCrew provided (including your field name).  Wrapping it with a Switch function will give you a True / False value rather than 0,-1.

Switch(Regex_countmatches([replacement order#],"[^0-9]")=0,[replacement order#],0,'False',-1,'True')

[MarqueeCrew]

If you drop that formula into a filter directly it will separate the data too

[GaryKennedy]

Thank you for formula, this is almost perfect.

I want [replacement] to show "True" when "40116486, 142698960" is in the "replacement order#" field it is currently showing False. I want  "40116486, 142698960" to continue to show in the "replacement order#" field as they are for two valid order numbers. When I do parse it seems to remove one of the numbers which is not what I want.

Also I want False to show in the replacement field for [null] fields.

I really appreciate the help!

[MarqueeCrew · Accepted solution]

Switch(Regex_countmatches([replacement order#],"[^0-9\s,]")=0,[replacement order#],0,'False',-1,'True')

As you wish

[GaryKennedy]

Brilliant! One last minor detail, any idea how to get the [null] from replacement order# field to show as False in the replacement field? It currently shows as True.