Regex - extract number using formula instead of regex tool

[AshM]

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

[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!

[Christina_H · Accepted solution]

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

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

[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?