Filtering using wildcards

[Carolina18]

Hi everyone! I hope someone could help me...´cause I´m not finding the solution 😞

I want to filter the attached file in order to split the rows as follow:

   a) Rows with DK (column C) that response to the follow pattern Z*Z*, Z*Y*, Z*W*, Z*X*

   b) Rows with DK (column C) that don´t  response to the previous pattern (Z*Z*, Z*Y*, Z*W*, Z*X*)

I hope my explanation was clear enough for you to help me 🙂
Thank you so much in advance!

[Luke_C]

Hi @Carolina18 

Regex_match can help you. Here's an example:

REGEX_Match([DK], 'Z.Z.|Z.Y.|Z.W.|Z.X.')

This formula looks for the patterns you specified. A period represents any single character. The pipe represents 'OR'. 

[isabella_cheung]

Update: solved: used Replace() instead of REGEX_replace()

Hi @Luke_C

I am running into a problem stemming from what you just described. I am building out an analytical app where the app cleans up an excel file that the user submits by removing a phrase that the user specifies. Say the submitted file is below: 

column 1	column2
abc.123	jobrole
123abc	rolejob
abc123	job.role
ab123c	job_role
1a2bc3	_rolejob

And the user wants to remove the phrase ".role" from column 2. What I do is I pass the user input into a REGEX_Replace([USER_SPECIFIED_COLUMN_NAME], "USER_SEPCIFIED_VALUE", "") So with the user inputs from this example that becomes REGEX_REPLACE([column2, ".role", "") and this is the output 

column 1	column2	New
abc.123	jobrole	jo
123abc	rolejob	rolejob
abc123	job.role	job
ab123c	job_role	job
1a2bc3	_rolejob	job

This is happening due to the period being read as a single character wildcard. The desired outcome is below: 

column 1	column2	New
abc.123	jobrole	jobrole
123abc	rolejob	rolejob
abc123	job.role	job
ab123c	job_role	job_role
1a2bc3	_rolejob	_rolejob

Is there a workaround to this you know of so that the period is not read as a single character wildcard? Thank you in advance!

[Luke_C]

Hi @isabella_cheung 

Try this:

REGEX_REPLACE([column2], "\.role", "")

The \ acts as an escape character to indicate that the formula should treat the period as a literal period rather than a character in the regex syntax