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.

Workflow

Developer

Developer Tools

Regex

Accepted answers

grossal

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

BenMoss

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

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

All comments

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

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!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177