I am trying to delete specific rows within a column. My data file keeps repeating the below highlighted in yellow rows and I was wondering if I can create a filter or function that will delete these rows.
There are various ways to do this, one way that I would suggest is to use the filter tool in combination with the LEFT function. Your custom expression in the filter tool should look something like:
LEFT([FieldName],19) = "ACS SECURITY REPORT" OR LEFT([FieldName],8) = "POST ACS" OR LEFT([FieldName],11) = "ID INITIALS"
As I said, there are multiple ways of doing this, so it may not be the best method for you, but it should get on you track to a solution that meets your needs.
Hi @mustufa2019
Without a sample to work with, not sure if this would work directly with your actual data, but REGEX is how I'd approach this. Using a REGEX_MATCH() function in a filter would split the records that matched the specified pattern.
Here is the REGEXpression i entered into the Filter. You'll need to be sure you update the "[Field1]" to whatever your field name is.
REGEX_MATCH([Field1], '^[A-Z]+\s+[A-Z]+\s+.+\s+[A-Z]{2}\d+\s+[A-Z]+\s+.+$')
JimmyTeknion Data Solutions
@nerces Thank you so much, this worked! I have question in regards to the scripts:
LEFT([SJOB: JG541ACS RUN DATE: 04/27/19 TIME: 1:58:31 BANK: 541 ID: 541 ],19) = "ACS SECURITY REPORT" OR
What does the "19" in the above script refer to?
@jrgo thank you so much! this worked too but i'm trying to understand the logic of this script
