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

Filter rows with numbers or special characters

RKSPREDDY
6 - Meteoroid

Hi, I am relatively new to the Alteryx. I have a problem statement, kindly suggest me the solution.

 

I need to filter rows with either numbers or special characters. Below is my data. In these I need to filter out Ken7, Ken@, Palliser!, Perry^, etc.

First Name
Ken7
Ken@
Ken
Ken
Palliser
Palliser
Palliser!
Palliser
Donald
Peter
Madeleine
Jane
Perry^
Steve
Steve
Raymond_
Christian
Doris&
Hana
Angela*
Angela
Elena
Joseph
Joseph
Joseph
Joseph%
Jack
Jack
Jack
Janice
Edith$
Franklin
Arlene
Sumner
Sharon
Robert1
Robert
7 REPLIES 7
Thableaus
17 - Castor
17 - Castor

Hi @RKSPREDDY 

 

Use Filter Tool in Custom Filter mode with this RegEX formula:

REGEX_MATCH([First Name], "^[a-zA-Z]+$")

 

True Side will be all of your "only letters" based words

False Side will contain rows with special characters and numbers.

 

Cheers,

princejindal
9 - Comet

Check out my solution. A quick work around :)

But I am sure there are other better ways

RKSPREDDY
6 - Meteoroid

Thanks a lot Thableaus, it's working but it is also filtering rows with dot (.) and space. For me names with dot & space are acceptable. Can you please suggest how can I keep these records out from filtering.

princejindal
9 - Comet

This should solve it.

REGEX_MATCH([First Name], "^[a-zA-Z. ]+$")

 

Note that I have added (.) and space to the formula suggested by @Thableaus 

Thableaus
17 - Castor
17 - Castor

@RKSPREDDY 

 

EDIT: REGEX_MATCH([First Name], "^\s?[a-zA-Z][a-zA-Z.\s]*$")

 

This assures that the row starts with a letter and has letters. The other formula can get rows with "...." or "    ".

 

@princejindal it's important to label space as "\s" character.


Cheers,

RKSPREDDY
6 - Meteoroid

Thanks a lot Thableaus, it's working perfectly. Thanks @princejindal, appreciate both of your time on providing solution.

Present_guy
8 - Asteroid

That is so clever! Thanks for sharing this! 

Labels