Filtering Numbers Out of a Column

[amateur101]

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?

[CharlieS · Accepted solution]

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.

[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

[aeulusan]

thanks

[Riclets]

A simple solution is always the best solution. However, when I used it on my data set the RegEx formula would exclude decimal numbers shown below. Can the RegEx formula be tweaked further? Thanks.

0.0000535464966035295
0.000007978683289793
0.00280975422430058