We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Converting String w/Symbol to Numeric

knnwndlm
8 - Asteroid

Hello,

 

I suspect that I'm having a tough time converting a string such as $1000 and/or 5.23% into their numeric form as 1000 and 5.23 using ToNumber because I need to remove the $ and % sign first. 

 

Could somebody please help me figure out how to parse the signs out using RegEx?

 

Thank you,

Konn

9 REPLIES 9
DataNath
17 - Castor
17 - Castor

You can use the following to parse the group of characters that is only a number or full stop (for percentages like you say). You can also change the outcome data type in the dropdown next to the capture group name :

 

 

([0-9\.]+)

 

 

DataNath_1-1652835995284.png

knnwndlm
8 - Asteroid

Thank you for the quick response.  Since I want to replace the column, I would use the Replace Method, but what do I need to put in the Replacement Text?  Not sure what I understand what that represents.  

 

Thanks,

Konn

knnwndlm
8 - Asteroid

I noticed that the expression truncated my string from 203,305,333 to 203.  How can I modify it so that it only takes away the $ sign and not the rest of the expression?

DataNath
17 - Castor
17 - Castor

No problem @knnwndlm. If you want to replace then just use '$1' as the Replacement Text. This is what you'll be replacing what you've extracted with, which in this case is the first capture group ($1) - the numbers (without their symbols). Make sure you have 'Copy unmatched text to output.' unticked, otherwise it'll also bring in things that weren't matched & extracted (i.e. your %/$ symbols). Also, I figured you may have large numbers, sometimes with commas and so I've just added that to the capture group. If this isn't the case then obviously just remove it!

 

 

 

([0-9,\.]+)

 

 

 

DataNath_0-1652836595116.png

 

DataNath
17 - Castor
17 - Castor

One other option is of course to just use a formula tool as shown below. However, this isn't as dynamic. If you'll only ever have '$' or '%' outside of numbers/commas/decimal points then this is fine, but each time there's another possible character (you can see from the £ I've added to the example), you'll need to add another 'elseif' statement for that.

 

if Contains([Test 1], '$') then Replace([Test 1], '$', '')
elseif Contains([Test 1], '%') then Replace([Test 1], '%', '')
else [Test 1] endif

 

DataNath_0-1652837050181.png

 

binuacs
21 - Polaris

@knnwndlm Another option is to use the ReplaceChar function

 

binuacs_0-1652872753839.png

 

knnwndlm
8 - Asteroid

Thank you Both!  Appreciate it!

knnwndlm
8 - Asteroid

Why is the Replacement Text $1?

DataNath
17 - Castor
17 - Castor

@knnwndlm when we apply the RegEx, anything between () is a capture group i.e. what's being pulled out. The $1 just references these, with $1 being the first. If we had a large RegEx expression with, say, 5 capture groups, and you wanted to replace your whole field with the third one, then you would use $3, as an example.

Labels
Top Solution Authors