Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Filtering Numbers Out of a Column

amateur101
6 - Meteoroid

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?

10 REPLIES 10
CharlieS
17 - Castor
17 - Castor

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
14 - Magnetar

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
17 - Castor
17 - Castor

@Claje wrote:

I would always recommend @CharlieS' solution


I should frame that.

aeulusan
7 - Meteor

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
17 - Castor
17 - Castor

Hi @aeulusan 

 

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

 

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

aeulusan
7 - Meteor

@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
17 - Castor
17 - Castor

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
7 - Meteor

thanks

Riclets
5 - Atom

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

Labels