can I select records for a given middle name or substrings?

[lag]

I need to recover some records from an older Excel table ('97 release)
having only 2 fields:
- 1st column is for persons' full name (first name, middle name,
surname, title)
- 2nd column is for their contact phones, including mobiles

I need to select /filter in a list to print or store separately, only
those records containing a given middle name, title or having mobile
numbers.

How can be designed a proper formula (in Data-> Filter -> Advanced ?)
to force filtering the list with this kind of criteria involving
substrings like above ?

As sample how can select only the persons having John as middle name,
in order to send them greetings by special name aniversary occassions?

SAMPLE:
the 1st column may be something alike:
1- Martis *John* Suvley
2- Lucifer Tim Volsaki
3- Anna Doris Fletcher
4- Tommy *John* Forester
5- Adrian B. Dolmonsky
etc
Final demand is to list only the persons who also named John, e.g.
records 1 and 4 etc.

Can Excel manage data columns inside, e.g. applying criteria to
substrings ? What operators must be called in order to manage these
substrings or alike ?
(another sample is to grab only persons who have mobiles cause mobile
numbers, differing from fixed phone numbers, could have a given common
prefix provided by their network)

Greetings!

 

[Otto Moehrbach]

Lag
One way:
You could use some extra columns to determine if the names and numbers
have the criteria you specify, then filter on those columns. For instance,
let's say the names are in Column A starting in A1 and the phone numbers are
in Column B. In cell C1 you could put:
=IF(ISERROR(FIND("John",A1)),"No","Yes")

But this would find "John" in the first name also and will find John
in Johnson. Putting a space before and after John in the formula would
limit the search to only middle names of John, as in:

=IF(ISERROR(FIND(" John ",A1)),"No","Yes").



If you want to search for first names use:

=IF(LEFT(A1,4)="John","Yes","No").



Searching for a phone number with a constant prefix, say 123, use:

=IF(LEFT(A1,3=123,"Yes","No")



HTH Otto

 

[Kenneth E. Coakley]

A simple way to filter your list on middle names is to do a 'custom'
AutoFilter on the column using:

contains: John

as the criteria. To eliminate those names whose first names are John (but
not their middle names) you can also add:

AND

does not start with: John

Of course, this will only work if nobody has a first name and middle name of
"John".

Ken

 

[lag]

Thank you all for very prompt and useful reply !

By the way, yesterday evening late, I gave me one more try with these
AutoFilter ON (Data->Filter->AutoFilter).

I remind to try selecting [Custom...](filter), followed by properly
completing new box opened for filter's criteria.

By simply selecting [contains] and then typing [John], solved my
demands immediately like you suggested in your great reply!

Thank you again anyway!