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
Solved! Go to Solution.
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
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?
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,\.]+)
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
Thank you Both! Appreciate it!
Why is the Replacement Text $1?
@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.
User | Count |
---|---|
52 | |
27 | |
25 | |
24 | |
21 |