Regex - extract number using formula instead of regex tool

Hello

I have a simple piece of text that I need to extract numbers from. In (pic1) you can see it extracts the number (148.71) and inserts to new column, which is perfect and what I need it to do. However I will now need to do this same extract for 8 more columns and don't want to use regex tool x8 if I can help it.

I tried to enter the same regex using the formula tool (pic2) and do a regex_match. The 'data preview' shows a '0' I thought using match would check and return if true.

Is there a way to use formula tool?, bearing in mind I need to do this same regex on 8 more columns.

Thanks

1.PNG

2.PNG

Regex

Accepted answers

Christina_H

RegEx_Match generated a Boolean, telling you whether the input matches the pattern. For an equivalent to Parse you can use RegEx_Replace:

REGEX_Replace([Field1], "\D+([\d\,\.]+)", "$1")

To apply this to multiple fields, try putting it in a multi-field formula tool

RegEx multifield.yxmd

All comments

DataNath

Hey @AshM, Regex_Match is just a boolean that checks whether or not the entire string matches the pattern you've provided. In this case it doesn't and so it returns 0 (false). In terms of extracting, you could potentially use something like this:

This works for your single example, but all depends on whether other fields/records are the same. If not, we may need to revisit and so more examples would be great if this doesn't apply.

You'll note that I've also provided a non-regex solution as once again, if all of your data matches the single example, you can just handle this with standard string functions.

Hope this helps!

Extract value with regex in Formula.yxmd

Christina_H

RegEx_Match generated a Boolean, telling you whether the input matches the pattern. For an equivalent to Parse you can use RegEx_Replace:

REGEX_Replace([Field1], "\D+([\d\,\.]+)", "$1")

To apply this to multiple fields, try putting it in a multi-field formula tool

RegEx multifield.yxmd

AshM

Hi Nathan @DataNath ,
That was fast, thanks

So your first regex (Output) is what I need since it takes the number out and don't have to worry about the text in the field and order of text. I previously attempted using regex_replace and failed and what I noticed is you got it to work with the addition of "[^\d]+" in front that's good to know.

Since I need the new field to be a field that I can sum up and do calculations on, I have changed 'Data type' to double and added the ToNumber in front, I get the lost information in conversion, is this expected? Or how to get round that error?

Thanks a million.

3.PNG

AshM

@Christina_H

Oooh thanks. I have amended the expression to include "ToNumber(REGEX_Replace([_CurrentField_], "\D+([\d\,\.]+)", "$1"))" and seems to work without error or lost in conversion when changing output type to "Double"

Thank you..

DataNath

Can't seem to replicate the issue @AshM - in your error message there's a pilcrow after the final number so may be a trailing character or something?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703