Delete/Filter Rows in Which a Cell Doesn't Meet a Specific Format

[shaheer]

I have a set of data that looks like the following:

Input:

Project Name	Open?
1000000423	Yes
2021005821	No
2022-MF-TM-328	Yes

I wish to remove all rows in which the Project is not a 10 digit value.

Output:

Project Name	Open?
1000000423	Yes
2021005821	No

I have tried the following using the filer tool, but it did not work. I have tried a few different methods with no success.

Next, after filtering the data, I want to add two letters PR infront of the project name values to make it look like the following:

Project Name	Open?
PR1000000423	Yes
PR2021005821	No

Follow up question, where can I practice regular expression problems online? I want to get better at them. Thank you.

[Luke_C]

Hi @shaheer 

The basic filter does not support regex. 

You can do a custom filter: Length([Project Name]) != 10

[DataNath · Accepted solution]

Hey @shaheer the first step would be to perform a filter based on a regex_match() of 10 digits:

REGEX_Match([Project Name], '\d{10}')

To add 'PR' to the front of these values, that's a simple addition expression:

To learn/practice RegEx, I'd recommend watching the parsing Interactive lessons - https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...

There's also a great guide here that @MeganBowers uploaded yesterday - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/New-Resource-Guide-for-RegEx-Text-Pars...

For testing expressions before using them, sites like https://regex101.com/ are brilliant.

[shaheer]

But if I do length != 10, it would include non-digit characters. I only want the 10 digit numbers.

[Luke_C]

Hi @shaheer Understood, @DataNath has you covered!

[shaheer]

Awesome, both of these work perfectly. Thank you so much and thanks for the links as well!