Filter to find string anywhere?

Vaughn
Vaughn Altair Community Member
edited January 2019 in Community Q&A
​I want to filter down data if it contains a certain word. For example if I have a bunch of names in a column.

Smith, John Rae
Johnson, Greg Allen
Mitchell, Tim Jean

I want to filter and see anything that contains "Greg"

How would I do that?

I have tried using things like

[Name]="*GREG*" no luck...

Any Help is appreciated.

------------------------------
Vaughn Mitchell
Data Analyst
Avera Health
NA
555-5555
------------------------------
Tagged:

Best Answer

  • Steve_Caiels
    Steve_Caiels
    Altair Employee
    edited January 2019 Answer ✓
    Vaughn said:

    I got this to work. However, it this the best way to do this.

    Instr("greg",Name)=4

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 01-15-2019 04:57 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I want to filter down data if it contains a certain word. For example if I have a bunch of names in a column.

    Smith, John Rae
    Johnson, Greg Allen
    Mitchell, Tim Jean

    I want to filter and see anything that contains "Greg"

    How would I do that?

    I have tried using things like

    [Name]="*GREG*" no luck...

    Any Help is appreciated.

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------
    "

    Hi Vaughn,

    The Instr is the way to go, but the '=4' specifies the position, so I wouldn't expect that to be working with the sample data you showed??

    Instr returns the position of the first hit it finds, or zero if it doesn't find a match.  When used without ='something' at the end, any record that has the value anywhere in the string will be returned in the filter.  When used *with* = 'something', only records which have the first match at that specific position will be returned.

    It will also get triggered for a partial match.  i.e. Assuming you remove the '=4', your matches will also include Gregory, McGregor etc.  To force a whole word match, you can add a space each side of the search string. Instr(" greg ",Name). 

    However, this would exclude rows that start or end with "greg" as they do not have the leading or trailing space.  You can get around this by adding spaces to the target string part of the filter.  Instr(" greg ",  " " + Name + " ")

    Regards,
    Steve.

    ------------------------------
    Steve Caiels
    Professional Service
    Datawatch Corporation
    +44 203 868 0253
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 01-15-2019 05:06 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I got this to work. However, it this the best way to do this.

    Instr("greg",Name)=4

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------

    Original Message:
    Sent: 01-15-2019 04:57 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I want to filter down data if it contains a certain word. For example if I have a bunch of names in a column.

    Smith, John Rae
    Johnson, Greg Allen
    Mitchell, Tim Jean

    I want to filter and see anything that contains "Greg"

    How would I do that?

    I have tried using things like

    [Name]="*GREG*" no luck...

    Any Help is appreciated.

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------"

Answers

  • Vaughn
    Vaughn Altair Community Member
    edited January 2019
    I got this to work. However, it this the best way to do this.

    Instr("greg",Name)=4

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 01-15-2019 04:57 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I want to filter down data if it contains a certain word. For example if I have a bunch of names in a column.

    Smith, John Rae
    Johnson, Greg Allen
    Mitchell, Tim Jean

    I want to filter and see anything that contains "Greg"

    How would I do that?

    I have tried using things like

    [Name]="*GREG*" no luck...

    Any Help is appreciated.

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------
    "
  • Steve_Caiels
    Steve_Caiels
    Altair Employee
    edited January 2019 Answer ✓
    Vaughn said:

    I got this to work. However, it this the best way to do this.

    Instr("greg",Name)=4

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 01-15-2019 04:57 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I want to filter down data if it contains a certain word. For example if I have a bunch of names in a column.

    Smith, John Rae
    Johnson, Greg Allen
    Mitchell, Tim Jean

    I want to filter and see anything that contains "Greg"

    How would I do that?

    I have tried using things like

    [Name]="*GREG*" no luck...

    Any Help is appreciated.

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------
    "

    Hi Vaughn,

    The Instr is the way to go, but the '=4' specifies the position, so I wouldn't expect that to be working with the sample data you showed??

    Instr returns the position of the first hit it finds, or zero if it doesn't find a match.  When used without ='something' at the end, any record that has the value anywhere in the string will be returned in the filter.  When used *with* = 'something', only records which have the first match at that specific position will be returned.

    It will also get triggered for a partial match.  i.e. Assuming you remove the '=4', your matches will also include Gregory, McGregor etc.  To force a whole word match, you can add a space each side of the search string. Instr(" greg ",Name). 

    However, this would exclude rows that start or end with "greg" as they do not have the leading or trailing space.  You can get around this by adding spaces to the target string part of the filter.  Instr(" greg ",  " " + Name + " ")

    Regards,
    Steve.

    ------------------------------
    Steve Caiels
    Professional Service
    Datawatch Corporation
    +44 203 868 0253
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 01-15-2019 05:06 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I got this to work. However, it this the best way to do this.

    Instr("greg",Name)=4

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------

    Original Message:
    Sent: 01-15-2019 04:57 PM
    From: Vaughn Mitchell
    Subject: Filter to find string anywhere?

    I want to filter down data if it contains a certain word. For example if I have a bunch of names in a column.

    Smith, John Rae
    Johnson, Greg Allen
    Mitchell, Tim Jean

    I want to filter and see anything that contains "Greg"

    How would I do that?

    I have tried using things like

    [Name]="*GREG*" no luck...

    Any Help is appreciated.

    ------------------------------
    Vaughn Mitchell
    Data Analyst
    Avera Health
    NA
    555-5555
    ------------------------------"

Categories