We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Regex - extract number using formula instead of regex tool

AshM
Alteryx
Alteryx

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

 

5 REPLIES 5
DataNath
17 - Castor
17 - Castor

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:

 

DataNath_0-1671461996370.png

 

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
14 - Magnetar

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

Christina_H_0-1671462163244.png

AshM
Alteryx
Alteryx

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
Alteryx
Alteryx

@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
17 - Castor
17 - Castor

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?

 

DataNath_0-1671463902569.png

Labels
Top Solution Authors