Extract number and return it

Hello everyone, I hope you fine all.
I have an simple case and I hope we all together solve it.
I have a column with string and some of columns have a numbers and another not.
I want this string with no digit return 1 , and string with numbers return the exist number.
Thanks in advance.

Hi @Analyst_1 ,

I wasn’t sure from your description if for those rows containing numbers, the number was embedded inside strings (e.g. “abc123def”), or was just a pure numeric. I have assumed it is potentially embedded as a single sequence of digits, and have used the following sample data
image

Here are two ways that spring to mind, both using regex.
image

The first approach uses a Regex Split with the following pattern
.*?([0-9]+).*?

This will return a single sequence of digits embedded in a string, and if not found returns a missing value.
image
After that, missing values can be turned to 1.

The second approach does the job with a single String Manipulation call,

This uses a regexReplace call to return the value of the column with all non-numerics replaced with empty strings. If the result is an empty string, it returns “1” but otherwise returns the value of the regexReplace.

image

Extract Number or return if not not present.knwf (13.3 KB)

Of course if your numbers are not integers we may need to do something different! :wink:

4 Likes

I would to thank you… your workflow is really helpful!!
but what about If I want to return 1 for no digit string, and another numeric string I want it returned as a missing values ?

Hi @Analyst_1 ,

Are you saying that you have strings. And if a string contains no number then you want to set it to 1, but if it contains a number then for some numbers you want to return missing value?

You could achieve this by adding a Rule Engine after the previous nodes. If none of the Rule Engine’s rules are satisfied, it returns missing value.

e.g. to have it return missing value if the value is 100, we tell it to return a value only if the value IS 100,

image


Extract Number or return if not not present 2.knwf (16.6 KB)

I hope that is what you meant, but if not please can you give an example data and expected output

Or if you meant that a string containing NO numerics should return “1” but ANY string containing numerics should return missing, you could do this directly from the same data using a Rule Engine:

image
NOT $column1$ MATCHES ".*?[0-9]+.*?" => "1"

image

2 Likes

Thank you @takbb
I really appreciate your help, It works!!
Thank you so much I am a student and you really help me.

2 Likes

You could also try toNull function inside string manipulation of @takbb s great solution to convert to missing.
Thanks a lot for reminding me to that “? :” syntax in string manipulation. I completely forgot
br

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.