Filter to find string anywhere?

Question

​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 · Accepted Answer

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