Excellent. That's just the kind of advice I was after. Thanks very much
for your time K.
"K Dales" wrote in message
...
1) In VBA: If Range("CheckCell").value like "*;d;*" Then...
- this lets you see if a d (with ; around it) is in the cell
referred
to by CheckCell, can be used in various ways to count or make decisions.
As an Excel formula, =COUNTIF(A:A,"*;d;*") will count how many times
the
";d;" pattern is in column A of your worksheet. Similar formulas will
accomplish other things.

2) You can use variations to find combinations:
- "*;do;*" only finds "do" together with no other letter before or
after
(within the given section of the cell contents marked out by the
delimiters)
- ";*do*" finds "do" together even if another letter follows, i.e.
would
find ";do;", ";dog;", ";doggone;", ";donut;", ...
- "*do*" would also find "do" in the middle of a word, like ";undone;"
You can use various combinations of these in various ways.

3) The benefit of the delimiters is in the ability to recognize adjacent
letters ONLY within the same 'word' - for example, if you had the words
"do"
and "good", no delimiters gives "dogood" and you would find the
combination
"*dog*"; but with the delimiters it is "do;good" and it would not find the
combination "*dog*".

4) You could certainly do this in many ways, but I find this technique to
be
easy and handy when trying to choose criteria from multiple related cells
and
I use it often.

As for performance, a filter is pretty slow, but all the calculations to
do
the concatenation and searches can also take a while. So there is a
tradeoff
and you would have to see what works best for you.

"John" wrote:

Hi there,

I currently have a spreadsheet of several thousand records each of which
is
categorised into five columns. What I'd like to do is to concatinate
the
category columns into one field so that I have five values in one cell,
split up by a comma or semi-colon (ie "f;t;d;o;l;r"). I then want to
search
on any instance of those categories

So here's the questions:

1) How should I write the string search to look for a particular letter
(ie
"see if cell C3 contains the letter 'd' ")

2) Would this change if I was searching for a double letter combination
(ie
'eg' for egg or, 'do' for dog)?

3) Is there a benefit to spliting up the field with a comma or a
semi-colon
(or anything else come to that)?

4) Could I achieve this from AdvancedSearch or AutoFilter? (I want the
criteria to be selected from a Form that the user can change)

The main problem at the moment if the file size's negative effect on
performance (particularly whilst using the existing AutoFilter.

Thanks in advance

John