Converting String w/Symbol to Numeric

[knnwndlm]

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

[DataNath]

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\.]+)

[knnwndlm]

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]

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 · Accepted solution]

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]

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

[binuacs · Accepted solution]

@knnwndlm Another option is to use the ReplaceChar function

[knnwndlm]

Thank you Both!  Appreciate it!

[knnwndlm]

Why is the Replacement Text $1?

[DataNath]

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