InStr with RegExp

[Fixed]

Hello!

When I'm trying to find some text in another it can be found as a part of word.
For example:

HappyText = "The best [B]cat[/B]alogue."
HappyPhrase = "[B]cat[/B]"
If InStr(HappyText, HappyPhrase) > 0

or

HappyText = "[B]Log[/B]ging into the cata[B]log[/B]ue."
HappyPhrase = "[B]log[/B]"
If InStr(HappyText, HappyPhrase) > 0

I want to find the same text/word:

HappyText = "You have a [B]log[/B] of the catalogue."
HappyPhrase = "[B]log[/B]"
If InStr(HappyText, HappyPhrase) > 0

I thing I should use the RexExp to find characters and digits left and right of HappyPhrase:
Something like this:

[A-Za-z0-9] & HappyPhrase & [A-Za-z0-9]

Please help with this problem!
Thank you in advance!

 

[Rick Rothstein]

Your question is not very clear to me. Are you asking to only find the HappyPhrase when it is part of another word but not when it stands alone as a word? Or are you asking the opposite, to only find the word when it is not part of another larger text string? What would help is several examples text strings, then tell us the HappyPhrase and finally show us what you want returned to you.

 

[Fixed]

Rick, thank you for helping me!

1. The correct searching results in these cases:

HappyPhrase = "[B]log[/B]"

HappyText = "You have a [B]log[/B] of the catalogue."
HappyText = "[B]Log[/B] in to..."
HappyText = "...;[B]log [/B]in to..."
HappyText = "...[B]log;...[/B]"
HappyText = "...[B]log.[/B]"

2. The incorrect searching results in these cases:

HappyPhrase = "[B]log[/B]"

HappyText = "You have the [B]logs[/B] of the catalogue."
HappyText = "[B]Log[/B]in ..."
HappyText = "...1[B]log [/B]in to..."
HappyText = "...[B]log[/B]1[B]...[/B]"
HappyText = "...b[B]log[/B]s."

 

[Rick Rothstein]

Here is a UDF that I developed a while ago that you can use. The function has two required parameters and two optional ones. The required first argument is the text you want to search (can be a quoted text string or a cell reference for a cell containing the text to search). The required second argument is the text you want to locate within the text passed into the first argument. The optional third argument, defaulted to 1, is the position to start searching from (this give you the ability to find the location of the first "find me" text and then use that, plus one, as the starting point in order to locate the position of a second occurrence of that "find me" text). The optional fourth argument allows you to specify whether the "find me" match will be case sensitive or not... True for a case sensitive match and False (the default) if the letter casing does not matter. Note... you do not have to specify the optional third argument if you want to start searching at postion 1 and want to specify a value for the fourth argument... the function will automatically recognize the data type of the third argument and act accordingly (that is, if you pass TRUE as the third argument, the code will assume that was meant for the MatchCase argument and make the assignment for you while simultaneously assigning 1 to the Start argument). Here is the code...

[table="width: 500"]
[tr]
	[td]Function InStrExact(ByVal SearchText As String, ByVal FindMe As String, _
                    Optional ByVal Start As Variant = 1, _
                    Optional ByVal MatchCase As Variant = False) As Long
  Dim X As Long, Str1 As String, Str2 As String, Pattern As String
  If TypeName(Start) = "Boolean" Then
    MatchCase = True
    Start = 1
  End If
  If MatchCase Then
    Pattern = "[!A-Za-z0-9]"
  Else
    SearchText = UCase(SearchText)
    FindMe = UCase(FindMe)
    Pattern = "[!A-Z0-9]"
  End If
  For X = Start To Len(SearchText) - Len(FindMe) + 1
    If Mid(" " & SearchText & " ", X, Len(FindMe) + 2) Like Pattern & FindMe & Pattern Then
      InStrExact = X
      Exit Function
    End If
  Next
End Function
[/td]
[/tr]
[/table]

 

[GTO]

...
I thing I should use the RexExp to find characters and digits left and right of HappyPhrase:
Something like this:

[A-Za-z0-9] & HappyPhrase & [A-Za-z0-9]

Please help with this problem!
Thank you in advance!

Hi there,

I'm with Rick in being unclear on the goal. In the above, you indicate you DO want to find 'buried' matches, that is for the search term "log", login would match. In your last post however, you seem to be saying that the first three letters in "login" should not match "log". If this is the case, you probably simply need to ensure word boundary (\b) before and after the text sought.

Hope that helps,

Mark

 

[Fixed]

Thank you Rick for sharing this amazing UDF! I'm sure it should help me!

 

[Fixed]

Sorry Rick for some nebulosity.
"that the first three letters in "login" should not match "log" - that is what I tried to say.

"If this is the case, you probably simply need to ensure word boundary (\b) before and after the text sought."
Could you please show an example?

 

[Rick Rothstein]

Sorry Rick for some nebulosity.
"that the first three letters in "login" should not match "log" - that is what I tried to say.

"If this is the case, you probably simply need to ensure word boundary (\b) before and after the text sought."
Could you please show an example?

I did not ask you this question... Mark (GTO) did.

Have you had a chance to try the UDF that I posted yet?

 

[Fixed]

I did not ask you this question... Mark (GTO) did.

Sorry Rick I knew it but...

Have you had a chance to try the UDF that I posted yet?

Yeah I've tried - it works perfectly!
Now I'm thinking about how to add your UDF into my UDF

 

[GTO]

...
"If this is the case, you probably simply need to ensure word boundary (\b) before and after the text sought."
Could you please show an example?

Presuming that last bit was aimed at me, sure.

Sheet1

	A
1	I must login.
2	I should logoff.
3	I sawed logs all day.
4	I stepped over a big log while hiking.

<thead>
</thead><tbody>
</tbody>

Excel 2010



In a Standard module...

Option Explicit
  
Sub example()
Dim REX As VBScript_RegExp_55.RegExp
Dim rexMatch As VBScript_RegExp_55.Match
Dim Cell As Range
  
  Set REX = CreateObject("VBScript.RegExp")
  With REX
    .Global = False
    .IgnoreCase = True
    .Pattern = "\blog\b"
    For Each Cell In Sheet1.Range("A1:A4").Cells
      If .Test(Cell.Value) Then
        Set rexMatch = .Execute(Cell.Value)(0)
        With Cell.Characters(rexMatch.FirstIndex + 1, rexMatch.Length).Font
          .Color = RGB(255, 0, 0)
          .Bold = True
          .Underline = True
        End With
      End If
    Next
  End With
End Sub

Only 'log' in row 4 will be changed in the above. You will need to set a reference to 'Microsoft VBScript Regular Expressions 5.5', or, change the first two variables to AS Object.

Hope thta helps,

Mark