Alteryx designer Discussions

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

Converting a string into a number

Highlighted
5 - Atom

Hi guys - in Alteryx - how do you convert a string into a number?  Basically my data comes in as a string with a dollar sign from the souce and I want it converted to a number so I can aggregate it. 

 

Eg:

 

$1,234.50 should be 1234.50

 

Thanks

Highlighted
Alteryx Alumni (Retired)

You need to get the dollar sign and commas out of the string and then just use the ToNumber() function.

 

Something like this in a Formula tool...

 

ToNumber(Replace(Replace([Data], '$', ''), ',' , ''))

Highlighted
Alteryx Alumni (Retired)

To follow up on my previous post, as with most processes in Alteryx, this can be done a few different ways.

I am trying to learn better how to utilize RegEx for pattern matching kind of processes, so I have attached an example of at least three ways you could handle this in Alteryx. Two of them using RegEx. :smileyhappy:

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@RodL,

 

I've not opened your yxmd, but here is an expression that blindly replaces for you in a RegEx statement (it might already be what you provided, but I am editing without access to the yxmd file.

 

TONUMBER(REGEX_REPLACE([STRING],"[^0-9.]*",''))

 

It will look for anything not in the range of 0 to 9 or the decimal point and delete them from the string.  It then converts the string to a number.

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Alumni (Retired)

I went the route of...

 

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

 

...which replaces the Dollar Sign or Comma with nothing.

 

Yours is a bit more dynamic, although your's would eliminate a Hyphen or Parenthesis if needed for indicating a negative number, correct? And mine would still retain a Parenthesis which wouldn't convert to a number (realizing that neither are in the example from @mvangodung, but probably should be incorporated in the solution, right?  :smileywink: )

 

How about...

 

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

 

...which gives these results...

 

Replace Dollar and Comma.png

 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

good point about the negative.  I would want to keep that in my list of acceptable values.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Alteryx Alumni (Retired)

Just to make the solution complete, here is the modified workflow with the three possible methods that I offered earlier...but accomodating for negative numbers (indicated by either a hyphen or parentheses).

Highlighted
Alteryx Partner

Thanks - this was exactly what I needed.

And I will also look at the RegEd examples - helpful to know those too.

 

Highlighted
5 - Atom

I have been trying to figure out how to do this and was unsucessful. Thank you for the solution!

Highlighted
5 - Atom

Hello. I referred to your thread to solve my issue. I just started using alteryx and just try to convert 'V_WString' to 'Double' format. Would you mind telling me what to enter to RegEx?

Labels