Filtering Numbers Out of a Column

I have a set of data that looks something like this. it's currently formatted as a string.

23523words
words
88 words
1
2
3
4
5
8124words
6
7

I'd like to filter out only the columns with numbers (1,2,3,4,5,6,7). I've tried converting the string to a number and then applying some filters to clear stuff out, but that was unsuccessful.

Should I be considering a different kind of tool?

Best Practices

Accepted answers

CharlieS

Use the following formula in a Filter tool:

REGEX_Match([Field1],"\d+")

It will return a value of "true" (and therefore filters to the true output) if the contents of [Field1] match the RegEx "\d+". The expression is interpreted as "one or more digit characters", or that the field only contains numeric characters.

20180808-NumericFilter.yxmd

All comments

CharlieS

Use the following formula in a Filter tool:

REGEX_Match([Field1],"\d+")

20180808-NumericFilter.yxmd

Claje

I would always recommend @CharlieS' solution over the following option, but if for some reason REGEX was not desirable, converting the desired field to a FixedDecimal using a Select tool will make all of the fields which contain text NULL. Then a Filter could be used on !ISNULL([field1]) which could also get you there.

CharlieS

@Claje wrote:
I would always recommend @CharlieS' solution

I should frame that.

aeulusan

Hi @CharlieS ,

I tried your solution for my own data set, but it did not work for my negative values. Can you help how to include negatives in this expresssion?

CharlieS

Hi @aeulusan

Update the expression to the following to account for negative numbers:

REGEX_Match([Field1],"-?\d+")

aeulusan

@CharlieS thanks for the input, but a newer problem now: my data set has non-integer values like (123.23, -345.44789). How can I include them as well? Right now, they get filtered out.

CharlieS

No problemo.

REGEX_Match([Field1],"-?\d+(\.\d+)?")

RegEx breakdown:

- = '-' character

? = optional (referring to the '-' that precedes it)

\d+ = one or more digit (numeric) character

\. = '.' character

(\.\d+)? = a decimal place and more digit characters are optional

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703