B
BizMark
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!)
_____________________________________________________________________________
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!)
_____________________________________________________________________________