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 a string into a number

mvangodung
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

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

RodL
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. Smiley Happy

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
RodL
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?  Smiley Wink )

 

How about...

 

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

 

...which gives these results...

 

Replace Dollar and Comma.png

 

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
RodL
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).

brendafos
10 - Fireball

Thanks - this was exactly what I needed.

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

 

virginia_long
5 - Atom

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

hideoh
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