Converting a string into a number

[mvangodung]

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

[RodL · Accepted solution]

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], '$', ''), ',' , ''))

[RodL]

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. 

[MarqueeCrew]

@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

[RodL]

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?   )

How about...

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

...which gives these results...

[MarqueeCrew]

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

[RodL · Accepted solution]

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).

[brendafos]

Thanks - this was exactly what I needed.

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

[virginia_long]

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

[hideoh]

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?