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!

 

[Fennek]

Hallo,

to identify the position of a string with RegEx, this UDF can help:

'reference to MS Regular Expression 5.5
Function Pos(strTxt As String) As Integer
Dim RegEx As RegExp
Set RegEx = CreateObject("vbscript.regexp")

With RegEx
   .Global = True
   .Pattern = "\d{5}\s"
   Set RR = .Execute(strTxt)
    For Each R In RR
        Pos = R.firstindex
   Next R
End With
End Function

regards

 

[Rick Rothstein]

Yeah I've tried - it works perfectly!

Great!

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

Why add it... just include it in the module along with your UDF and have your UDF call it directly within its code.

 

[Fixed]

I guess it's impossible

Can I show the file with the UDF (there are examples of source and result information) or only the code?

 

[Fixed]

Hope that helps,

Mark

I will try it, thank you!

 

[Fixed]

to identify the position of a string with RegExp, this UDF can help:

Fennek, I will try it, thank you a lot!

 

[Rick Rothstein]

I guess it's impossible

Can I show the file with the UDF (there are examples of source and result information) or only the code?

Who are you responding to? If me, I don't understand what the problem is... why can't you just include my UDF along with your UDF in the same module and use my UDF as a normal function and call it from within your UDF when you need it output?

 

[Fixed]

I guess it's impossible

Function NameTypeSort(Data_Range As Range, TextName As Variant, TextType As Variant) As Variant
Dim Cell
Dim Current_Row As Integer
Dim No_Of_Rows_in_Range As Integer
Dim No_of_Cols_in_Range As Integer
Dim Matching_Row As Integer
Dim ColO As Integer
Dim ColTw As Integer
Dim ColTh As Integer
Dim TypeNameO As Variant
Dim TypeNameTw As Variant
Dim TypeNameTh As Variant
Dim NumberOne As Integer
Dim NumberTwo As Integer
Dim NumberThree As Integer
NameTypeSort = CVErr(xlErrNA)
Matching_Row = 0
Current_Row = 1
ColO = 2
ColTw = 3
ColTh = 4
No_Of_Rows_in_Range = Data_Range.Rows.Count
No_of_Cols_in_Range = Data_Range.Columns.Count
       
If (ColO > No_of_Cols_in_Range) Then
    NameTypeSort = CVErr(xlErrRef)
End If
If (ColTw > No_of_Cols_in_Range) Then
    NameTypeSort = CVErr(xlErrRef)
End If
If (ColTh > No_of_Cols_in_Range) Then
    NameTypeSort = CVErr(xlErrRef)
End If
    '==========================================
    
If (ColO <= No_of_Cols_in_Range) Then
    Do
    If (Data_Range.Cells(Current_Row, 1).Value = TextType) Then
        Matching_Row = Current_Row
         End If
    
    Current_Row = Current_Row + 1
    Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))
    
        '==========================================
    If Matching_Row <> 0 Then
    TypeNameO = Data_Range.Cells(Matching_Row, ColO)
    If TypeNameO > 0 Then
    If InStr(Application.Substitute(TextName, TypeNameO, "", 1), TypeNameO) > 0 Then
    
    NumberOne = 100
    
    Else
    
    If InStr(TextName, TypeNameO) > 0 Then
    
    NumberOne = 1
    
    Else
    
    NumberOne = 0
    
       End If
       End If
       End If
    
    '==========================================
     If (ColTw <= No_of_Cols_in_Range) Then
    Do
    If (Data_Range.Cells(Current_Row, 1).Value = TextType) Then
        Matching_Row = Current_Row
         End If
    
    Current_Row = Current_Row + 1
    
    Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))
    
        '==========================================
    If Matching_Row <> 0 Then
    TypeNameTw = Data_Range.Cells(Matching_Row, ColTw)
    If TypeNameTw > 0 Then
    If InStr(Application.Substitute(TextName, TypeNameTw, "", 1), TypeNameTw) > 0 Then
    
    NumberTwo = 100
    
    Else
    
    If InStr(TextName, TypeNameTw) > 0 Then
    
    NumberTwo = 1
    
    Else
    
    NumberTwo = 0
    
       End If
       End If
       End If
    
    '==========================================
    
    If (ColTh <= No_of_Cols_in_Range) Then
    Do
    If (Data_Range.Cells(Current_Row, 1).Value = TextType) Then
        Matching_Row = Current_Row
         End If
    
    Current_Row = Current_Row + 1
    
    Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0))
    
    '==========================================
    If Matching_Row <> 0 Then
    TypeNameTh = Data_Range.Cells(Matching_Row, ColTh)
    If TypeNameTh > 0 Then
    If InStr(Application.Substitute(TextName, TypeNameTh, "", 1), TypeNameTh) > 0 Then
    
    NumberThree = 100
    
    Else
    
    If InStr(TextName, TypeNameTh) > 0 Then
    
    NumberThree = 1
    
    Else
    
    NumberThree = 0
    
       End If
       End If
       End If
       
    '==========================================
       
       NameTypeSort = CVar(NumberOne + NumberTwo + NumberThree)
             
       Select Case NameTypeSort
       Case 0
       NameTypeSort = "NF"
       Case 1
       If NumberOne = 1 Then
       NameTypeSort = TypeNameO
       Else
       If NumberTwo = 1 Then
       NameTypeSort = TypeNameTw
       Else
       If NumberThree = 1 Then
       NameTypeSort = TypeNameTh
       End If
       End If
       End If
       Case 2
       NameTypeSort = "MDN"
       Case 3
       NameTypeSort = "MDN"
       Case Is > 99
       NameTypeSort = "MN"
       
       End Select
       End If
       End If
       End If
       End If
       End If
       End If
End Function

 

[Fixed]

Who are you responding to? If me, I don't understand what the problem is... why can't you just include my UDF along with your UDF in the same module and use my UDF as a normal function and call it from within your UDF when you need it output?

Yes Rick, to you, I didn't see the another answers...

 

[Rick Rothstein]

That is a lot of code to digest, especially since I am about to go to sleep for the "night". Just taking a guess, though, but perhaps you would want to use my function in those instances where you use the InStr function. For example, you have this line of code...

If InStr(TextName, TypeNameO) > 0 Then

Maybe you would want to change it to this...

If InStrExact(TextName, TypeNameO, False) > 0 Then

Note: The third argument will be interpreted for the MatchCase argument and make the search case insensitive. I realize your use of the InStr function was for a case sensitive search, but your earlier examples seemed to indicate you wanted a case insensitive search to take place.

 

[Fixed]

Thank you Rick, I will try this construction "If InStrExact(TextName, TypeNameO, False) > 0 Then".
I guess your UDF should be added as the separate Module?
"but your earlier examples seemed to indicate you wanted a case insensitive search to take place."
Yes.