Searching for a substring inside another string - there has to be an easier way!

[Morris]

Hi. I'm looking for ans instance of the substring "tab" inside an
rcvdPack string, but I need to be sure it matches a whole word only, so
if it finds "tablets", "tabs", "protetab", or "taberculosis" it mustn't
replace those. I'm checking if a char before T and char after B is
different from a lowercase letter, but I think for what it does there
should be ans easier method. The mostly are spaces before T and after
B, but there might be brackets, fullstops, asterisks, etc. Sometimes
the the word TAB is put at the end of the rcvdPack that's why I check
the k + 2 >= Len(rcvdPack)

That's my code:


k = InStr(1, rcvdPack, "tab") ' replace TAB but not where TABS is
present
If k > 0 Then
If Not Asc(Mid(rcvdPack, k - 1, 1)) < 97 And Not
Asc(Mid(rcvdPack, k - 1, 1)) > 122 Then
'
Else
If k + 2 >= Len(rcvdPack) Then
rcvdPack = Replace(rcvdPack, "tab", "tablets")
ElseIf Not Asc(Mid(rcvdPack, k + 3, 1)) >= 97 And Not
Asc(Mid(rcvdPack, k + 3, 1)) <= 122 Then
rcvdPack = Replace(rcvdPack, "tab", "tablets")
End If
End If
End If


Any ideas?

Cheers

 

[Sri]

Instr always finds the instance of the specific word you mention, but if you
want to match the whole world, then you may directly test it and not using
Instr...

If rcvdPack= "tab" then
......statements.....

 

[RB Smissaert]

One simple solution is to do it in Word.
You can launch that without having to make it visible.
Lookup OLE automation.

These bits of Word code will give you the idea:

Function GetDocumentIdentifiers() As Collection

Dim rngWord As Range
Dim oStoryRange As Range

Set GetDocumentIdentifiers = New Collection

For Each oStoryRange In ActiveDocument.StoryRanges
For Each rngWord In oStoryRange.Words
If Len(rngWord.Text) > 4 And UCase(Left$(rngWord.Text, 2)) = "QQ"
Then
MakeRealWord rngWord
GetDocumentIdentifiers.Add rngWord.Duplicate
End If
Next
Next

End Function

Sub MakeRealWord(rngWord As Range)
'strips these characters from the end of the range
'-------------------------------------------------
rngWord.MoveEndWhile Cset:=Chr(10) & Chr(13) & _
Chr(9) & Chr(11) & Chr(160) & _
Chr(32), _
count:=wdBackward

End Sub


RBS

 

[Morris]

Instr always finds the instance of the specific word you mention, but if you
want to match the whole world, then you may directly test it and not using
Instr...

If rcvdPack= "tab" then
.....statements.....

well, that's the example of the rcvdPack:

"btc pi singulair tab 10mg d80 28" so I can't compare it straight
away...

 

[Ron Rosenfeld]

Hi. I'm looking for ans instance of the substring "tab" inside an
rcvdPack string, but I need to be sure it matches a whole word only, so
if it finds "tablets", "tabs", "protetab", or "taberculosis" it mustn't
replace those. I'm checking if a char before T and char after B is
different from a lowercase letter, but I think for what it does there
should be ans easier method. The mostly are spaces before T and after
B, but there might be brackets, fullstops, asterisks, etc. Sometimes
the the word TAB is put at the end of the rcvdPack that's why I check
the k + 2 >= Len(rcvdPack)

That's my code:


k = InStr(1, rcvdPack, "tab") ' replace TAB but not where TABS is
present
If k > 0 Then
If Not Asc(Mid(rcvdPack, k - 1, 1)) < 97 And Not
Asc(Mid(rcvdPack, k - 1, 1)) > 122 Then
'
Else
If k + 2 >= Len(rcvdPack) Then
rcvdPack = Replace(rcvdPack, "tab", "tablets")
ElseIf Not Asc(Mid(rcvdPack, k + 3, 1)) >= 97 And Not
Asc(Mid(rcvdPack, k + 3, 1)) <= 122 Then
rcvdPack = Replace(rcvdPack, "tab", "tablets")
End If
End If
End If


Any ideas?

Cheers

You can use Regular Expressions.

First: Tools/References and set a reference to Microsoft VBScript Regular
Expressions 5.5

Then modify below to do what you want:

================================
Sub TabToTablets()
Dim objRegExp As RegExp
Const Pattern As String = "\btab\b" 'pattern is "tab" \b = word break
Const Replace As String = "tablets" 'replacement string
Dim rcvdPack As String

rcvdPack = " 5 tab 6 tabs 7 tab"

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(rcvdPack) = True) Then

'Replace tab with tablet
Debug.Print objRegExp.Replace(rcvdPack, Replace)

End If
End Sub
=========================

5 tablets 6 tabs 7 tablets

============================

The function replaced the first and last "tab" in rcvdPack but ignored the
"tabs"

-----------------------------------
With regard to setting the reference, my understanding is that the code will
run faster if the reference is set as described above. However, an alternative
would be to call it within the code. Depending on your circumstances, this may
be easier to implement:

========================================
Option Explicit

Sub TabToTablets()
Dim objRegExp As Object
Const Pattern As String = "\btab\b" 'pattern is "tab" \b = word break
Const Replace As String = "tablets" 'replacement string
Dim rcvdPack As String

rcvdPack = " 5 tab 6 tabs 7 tab"

' Create a regular expression object.
Set objRegExp = CreateObject("VBScript.RegExp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(rcvdPack) = True) Then

'Replace tab with tablet
Debug.Print objRegExp.Replace(rcvdPack, Replace)

End If

Set objRegExp = Nothing
End Sub
==============================


--ron

 

[Gary Keramidas]

why can't you search for ",tab," or " tab " (<space>tab<space>). if there's a
separator, it must be in front and behind the word tab.

 

[Morris]

That's absolutely perfect!!

I didn't realize there are regular expressions in VBA - and I like it,
evn though it needs an extra reference added.

Can you point me to any good help file? Website showing the
capabilities?

 

[Tom Ogilvy]

maybe something like this:

Sub ABC()
rcvdPack = "btc pi singulair TAB 10mg d80 28"
If rcvdPack Like "*[!a-z]tab[!a-z]*" Or _
rcvdPack Like "*[!A-Z]TAB[!A-Z]*" Then
Debug.Print "OK"
Else
Debug.Print "Not OK"
End If
End Sub

 

[Ron Rosenfeld]

That's absolutely perfect!!

I didn't realize there are regular expressions in VBA - and I like it,
evn though it needs an extra reference added.

Can you point me to any good help file? Website showing the
capabilities?

Glad to help. Thanks for the feedback.

There's some stuff on the Microsoft web site with regard to the VBA flavor.

http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb

http://msdn.microsoft.com/library/d...html/2380d458-3366-402b-996c-9363906a7353.asp

There are some limitations compared with Perl, for example. If I recall
correctly, VBA does not implement look behind assertions.

Also, Laurent Longre has a free add-in, morefunc.xll, which can be obtained
from http://xcell05.free.fr/

Some of the functions are Regular Expression types that can be used either in a
worksheet or in VBA. The add-in is easily distributed with workbooks. It's
major limitation, which I am told is an *.xll limitation, is that the strings
can be no longer than 255 characters. (It DOES enable the look behind
assertions, however).


--ron