Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting String to Number

hholland
7 - Meteor

I have two fields that are set as a string however we use them to input currency numbers into. (Example: $1,000.00 or ($2,000.00)). I tried using the ToNumber() function however that is not working. I'm assuming I can use the regex tool however I'm really not formula with that. Any suggestions would be great. Thanks Heather

7 REPLIES 7
NickSm
Alteryx
Alteryx

Hey @hholland 

 

The most straightforward way to change the data type of a field would be to use a Select tool, and changing the data type to any of the numeric options in the drop-down list. 

Though when working with currencies, the '$' and comma characters will always cause the field to revert to a string, so you may have to trim or remove those before trying to do any calculations with that field.  Then if your desired output is to be formatted as currency, you can transform your data back as the last step before output.

hholland
7 - Meteor

NickSm, Thanks for the response. I can't use trim as the same column could have $1,000 or ($1,000) so they have a different number of characters in front to be trimmed. My end result needs to be 1000.00 or -2000.00 so that it's an actual number that I can add to another amount field. I need to have the dollar sign, comma and parenthesis removed and leave the period so the number doesn't get change (example if period was removed: from $1,000.00 to 100000). This is why I assumed I would need to use the RegX tool, however I don't really know much about the tool. Thanks

NickSm
Alteryx
Alteryx

@hholland 

 

Got it.  The easiest way to do that will probably be in the Formula tool then - can reference this post here that has quite the explanation to it:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Everything-You-Need-to-Know-About-C...

 

Also attaching a quick sample workflow that you can take a look at that uses a formula and then changes the data type to a number with two decimal places.

 

 

 

 

hholland
7 - Meteor

NickSm, Thank you this was helpful. I got the ToNumber(replaceChar()) formula to work for the positive numbers however I cant figure out how to get it to work for the negative numbers because of the parenthesis (Example: ($2,000.00)). I created a example workflow to show you. Any suggestions on how to handle the numbers that need to be negative would be great.

afv2688
16 - Nebula
16 - Nebula

Hello @hholland,

 

Try using this:

 

REGEX_Replace([Field1], '[$|\)\,]', '')

 

ToNumber(REGEX_Replace([Classification Adj], '[\(]', '-'))

 

Untitled.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

NickSm
Alteryx
Alteryx

@hholland 

 

You're on the right track - just to deal with the negative numbers you'll want to make sure you do a Replace formula for the '(' character to a '-' and then you can use the ToNumber just as you have it.

 

Attaching an example of that with what you provided

 

 

hholland
7 - Meteor

That work! Thank you so much for your help

Labels