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
Solved! Go to Solution.
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!
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.
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..
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?