Get all the numbers including comma and dot in a string using Regex

[RichardAlt]

Hi Guys,

I need help, How can I get all the numbers including comma's and dot's in a string using Regex,

Currently my Regex is working but it only gets the number before comma or dot.

For Ex. I have this string, "This is a sample string 10,000.00".

After I ran the regex it will only get the 10. But I want is 10,000.00 like that.

This is my strings it has number.

and this is the output of regex.

This is my regex. 

[BenMoss]

I would use the replace mode and use the reflex statement [^,\.\d] which means replace anything that does not equal those values, then leave the replace with as blank.

ben

[RichardAlt]

Hi @BenMoss 

It works, But I need to output the regex result to new column.

[grossal · Accepted solution]

Hi @RichardAlt,

try this one:

Result: 

I'll also post it as a code snippet, this let's you copy it:

([\d,\.]+)

Let me know what you think.

Best

Alex 

[RichardAlt]

Thank you @grossal. This is what I want.

[RichardAlt]

Hi @grossal ,

I have another question, How can I convert to fixed decimal? Because when I tried to convert to fixed decimal it has no comma's and dot's again. Why I need to convert to fixed decimal? Because I need to multiply it to another data. Thankyou!

[BenMoss · Accepted solution]

In that case you could use a formula tool before the regex tool to create a copy of your field, or you can just create a new field as the regex_replace function exists in the formula tool.

Regex_replace([field],”[^,\.\d]”,””)

in my eyes you would remove the comma too, this is not a valid numeric thing and purely a format mask, which is probably causing the fixed decimal issue.

Ben

[grossal · Accepted solution]

You should be able to do this using: 

ToNumber(Replace([Regex_Output], ',', ''))

ToNumber can't handle the comma, therefore we have to remove it first and than convert it remaining part to a number. 

Example:

Output:

Does this work for you?

Best

Alex