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?
Solved! Go to 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.
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.
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?
Hi @aeulusan
Update the expression to the following to account for negative numbers:
REGEX_Match([Field1],"-?\d+")
@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.
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
thanks
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