Hi all,

I don't usually go about the business of foisting my homewritten code on people and saying "hey guys, use this ... it's good. Honest", but temptation got the better of me on this one.

It's a function called 'textmatchespattern' and it's something I've ended up using a LOT in various projects that I've done.

What it effectively does is perform a more sophisticated version of INSTR, but callable of course from a worksheet. It can be used to perform logical checks on strings with multiple words without having to use lots of nasty nested ANDs, ORs, MIDs, FINDs, SEARCHs and of course doubled-up IF(ISERROR( tests.

The syntax is

TEXTMATCHESPATTERN(tText, tPattern)

Where tText is the full string you want to test, and tPattern are the words you want to test the presence or absence of.

By default, the presence of ANY word in tPattern within tText will return TRUE. However, a tPattern of "Word1 &Word2" will only return TRUE if Word1 AND Word2 are found. Similarly, "Word1 #Word2" will only return TRUE if Word1 IS there and Word2 IS NOT.

As the tests go on, successive words override the result, so you could have
"Word1 #Word2 Word3" whereby the presence of Word3 will always return TRUE regardless of Word1 or Word2, but if Word3 is absent, the previous test on Word1 and Word2 will be the output result.

Parentheses (brackets) may be used to group parts of the expression, for instance
"Word1 &(Word2 Word3)" would mean, output TRUE IF Word1 is present AND EITHER Word2 OR Word3 is present. This would be directly equivalent to "Word2 Word3 &Word1" but reads more logically.

The function is particularly useful when used in lists and applied to a particular listed column - for instance, "Job Title". You could do a test for "Service &Manager" to return TRUE for "Customer Services Manager", "IT Services Manager", etc. or perhaps "Service &(Manager Executive)" to widen the net, so to speak.

Oh yes - partial matches count by default, so if tPattern is 'Service' then testing on 'Service', 'Services' or 'disservice' would return TRUE. To specify that a WHOLE word must be found, enclose in square brackets, e.g. '[Service]'.

By now I think you get the idea, so here is the code. I'd be happy for comments/suggestions, and of course a little comment line of acknowledgement if anyone uses it in paid work (Mark Boulton, London!)

__________________________________________________ ___________________________