Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Converting a string with a $ sign into a number

AngelaO
Alteryx
Alteryx
Created

Question

How do you convert a string with a $ sign into a number?

Answer

1. First remove the dollar sign and commas out of the string

2. Convert () to a negative sign "-"

3.ToNumber() function

Input:

numberIn.JPG

Output:

numberOut.JPG

There are many ways to do this in Alteryx, here are a few:

Example 1

Formula Tool - ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))

formula1.PNG

Example 2

Formula Tool -ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))

formula2.png

Example 3

RegEx Tool #1 - Replace\$|,|\) with Replacement Text "blank", then

RegEx Tool #2 - Replace \(with Replacement Text "-"

Select Tool - Change Type to Fixed Decimal

regex1.PNG

regex2.PNG

select.png

See attached workflow.

Attachments
Comments
Zandro
5 - Atom

Example #2 is the easiest!