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!)

__________________________________________________ ___________________________

Public AndOp(0 To 255) As Integer
'To remember the value of the Status flag during 'Find' operations, when parentheses are used. <<
Public FlagPos As Integer

Static Function TextMatchesPattern(tText, ByVal tPattern, Optional ByRef tWhatMatched As String)
'Compare text using a pattern in &#() format
'Return boolean True/False depending on whether the text matches the criteria
'NEW MAY-06: Return the phrases that made the match TRUE into the variable passed to tWhatMatched.

Dim otWhatMatched As String

If TypeName(tText) = "Range" Then
tText = tText.Text
End If

If tPattern &lt;&gt; LastErrorString Then
ExprErrorGiven = False
End If
If Right(tPattern, 1) &lt;&gt; " " Then
tPattern = tPattern & " "
End If

posSPACE = InStr(1, tPattern, " ")
posBRACE1 = InStr(1, tPattern, "[")
posBRACE2 = InStr(1, tPattern, "]")
While posBRACE2 &gt; posSPACE And Not (posBRACE1 &gt; posSPACE)
posSPACE = InStr(posBRACE2, tPattern, " ")
posBRACE1 = InStr(1, tPattern, "[")
posBRACE2 = InStr(1, tPattern, "]")
Wend

If Left(tPattern, 1) &lt;&gt; "#" Then
cStatus = False
Else
'If the pattern starts with a # character, take the rest of the string to mean
' "everything except..." .
cStatus = True
End If

oPattern = tPattern
FlagPos = 0
LastOp = 0 '1=And (&) 2=Not (#) last operand before bracket
Q = 0
AndOp(0) = 0 'Do an OR when cascading back to root paranthesis level
NewStatus = False 'Trial

While posSPACE &gt; 0
tPhrase = Left(tPattern, posSPACE - 1)

prebracketWhatMatched = otWhatMatched

If Mid(tPhrase, 2, 1) = "(" Then
'parentheses used to mark a part of the find criteria which will generate its own status
'so phrase must contain what came before AND any selection of what appears in the brackets
FlagPos = FlagPos + 1
FlagStack(FlagPos) = cStatus
If (Left(tPhrase, 1) = "&") Then
AndOp(FlagPos) = 1
Else
AndOp(FlagPos) = 2
End If
tPhrase = Mid(tPhrase, 3)
cStatus = False 'start decision making again within brackets
End If
If Left(tPhrase, 1) = "(" Then
'bracket without & or # - do an OR with the result of the bracketed expression and the rest
FlagPos = FlagPos + 1
FlagStack(FlagPos) = cStatus
AndOp(FlagPos) = 0 'no AND/NOT operand
tPhrase = Mid(tPhrase, 2)
cStatus = False 'start decision making again within brackets
End If
While Right(tPhrase, 1) = ")"
Q = Q + 1 'do close-off routine after checking text
tPhrase = Left(tPhrase, Len(tPhrase) - 1)
Wend

nBracePos = 1
If InStr(1, "#&(", Left(tPhrase, 1), 1) &gt; 0 Then
nBracePos = 2
End If
'Replace [ with a leading space and ] with a closing space
'so that [word] can be used to indicate 'find entire word'
If Mid(tPhrase, nBracePos, 1) = "[" Then
tPhrase = Left(tPhrase, nBracePos - 1) & " " & Mid(tPhrase, nBracePos + 1)
tText = " " & tText
End If
If Right(tPhrase, 1) = "]" Then
tPhrase = Left(tPhrase, Len(tPhrase) - 1) & " "
tText = tText & " "
End If

'For AND word
If Left(tPhrase, 1) = "&" Then
If InStr(1, tText, Mid(tPhrase, 2), 1) = 0 Then
cStatus = False
'otWhatMatched = ""
Else
'otWhatMatched = otWhatMatched & tPhrase & " "
End If
End If

'For NOT word
If Left(tPhrase, 1) = "#" Then
If InStr(1, tText, Mid(tPhrase, 2), 1) &lt;&gt; 0 Then
cStatus = False
'otWhatMatched = ""
Else
'otWhatMatched = otWhatMatched & tPhrase & " "
End If
End If

'For OR word
If InStr(1, tText, tPhrase, 1) &lt;&gt; 0 Then
If Not cStatus Then
cStatus = True
'otWhatMatched = otWhatMatched & tPhrase & " "
End If
End If

If cStatus Then
If Not ocStatus Then
otWhatMatched = otWhatMatched & tPhrase & " "
End If
Else
otWhatMatched = ""
End If
ocStatus = cStatus

While Q &gt; 0 'if closing off brackets, apply new status to previous one in stack
'close the bracketed section
NewStatus = cStatus 'The new status to apply to the one that was stored at the ( point

'check that stack hasn't been read past empty by too many close-brackets
If FlagPos &lt; 0 Then
If Not ExprErrorGiven Then
Beep
MsgBox ("There are too many close-brackets in your expression. Please try again."), vbExclamation
LastErrorString = oPattern
ExprErrorGiven = True
End If
Exit Function
End If

'Now get the last status
cStatus = FlagStack(FlagPos)
If AndOp(FlagPos) = 1 Then
cStatus = cStatus And NewStatus
Else
If AndOp(FlagPos) = 2 Then
cStatus = cStatus And (Not NewStatus)
Else
cStatus = cStatus Or NewStatus
End If
End If

If FlagPos &gt;= 0 Then
FlagPos = FlagPos - 1
End If
Q = Q - 1

'If Q = 0 And cStatus And otWhatMatched = "" Then
'otWhatMatched = prebracketWhatMatched
'End If
Wend



tPattern = Mid(tPattern, posSPACE + 1)
posSPACE = InStr(1, tPattern, " ")
posBRACE1 = InStr(1, tPattern, "[")
posBRACE2 = InStr(1, tPattern, "]")
While posBRACE2 &gt; posSPACE And Not (posBRACE1 &gt; posSPACE)
posSPACE = InStr(posBRACE2, tPattern, " ")
posBRACE1 = InStr(1, tPattern, "[")
posBRACE2 = InStr(1, tPattern, "]")
Wend
Wend
tPattern = oPattern
ExprErrorGiven = False
TextMatchesPattern = cStatus

If Not IsMissing(tWhatMatched) Then
tWhatMatched = otWhatMatched
End If
End Function

BizMark wrote:
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!)

__________________________________________________ ___________________________




--
BizMark

Hi BizMark,

Where's the code?

Ken Johnson

????????

Just look above....

M