Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be an easier way!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be
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..... -- Regards Sri "Morris" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be an easier way!
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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be
Sri wrote: 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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be an easier way!
On 15 Sep 2006 03:58:14 -0700, "Morris" wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be an easier way!
why can't you search for ",tab," or " tab " (<spacetab<space). if there's a
separator, it must be in front and behind the word tab. -- Gary "Morris" wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be an easier way!
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to
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 -- Regards, Tom Ogilvy "Morris" wrote: Sri wrote: 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... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a substring inside another string - there has to be an easier way!
On 15 Sep 2006 07:21:49 -0700, "Morris" wrote:
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...02&Product=vbb http://msdn.microsoft.com/library/de...63906a7353.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for a string of text inside a column | Excel Worksheet Functions | |||
Substring to replace string | Excel Programming | |||
Substring to replace string | Excel Programming | |||
Searching for a substring in a range | Excel Programming | |||
How to get rid of string which contain a substring like 'PO BOX'. | Excel Programming |