We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

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

RichardAlt
8 - Asteroid

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.

RichardAlt_0-1588695099430.png

 

and this is the output of regex.

RichardAlt_1-1588695139646.png

 

 

This is my regex. 

RichardAlt_2-1588695176814.png

 

 

7 REPLIES 7
BenMoss
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

Hi @BenMoss 

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

grossal
15 - Aurora
15 - Aurora

Hi @RichardAlt,

 

try this one:

 

grossal_0-1588695910633.png

 

Result

grossal_1-1588695921773.png

 

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
8 - Asteroid

Thank you @grossal. This is what I want.

RichardAlt
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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
15 - Aurora
15 - Aurora

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:

grossal_1-1588698162872.png

 

Output:

 

grossal_2-1588698172941.png

 

Does this work for you?

 

 

Best

Alex

Labels