• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using wildcard in UDF for Replace

E

Einarr

New Member
Hello, my first post so I hope I am doing this correctly

First thanks for considering my question

I have asked this question on different forum about a week ago and had no meaningful response so I am trying a different forum

I have the Function below that finds the nth occurrence of a substring in a string and it works great, but it will not take wildcards

And I desperately need to use it with wildcards

Can anyone see how to alter the function or the way I am using it that will permit me to use it with wildcards?

I have tried but have been no luck

Again thanks

Here is a common example of what I want to do, I have a column of data that looks like this (again this is only an example of a common need)

Also in this example, the position of the substring is 1, but other times it can be 2, or 5 or n

Code: 
Some Col
What types of activities have you engaged in with [Field-3]? You can choose multiple answers. - What types of activities have you engaged in with [Field-3]? You can choose multiple answers. - Selected Choice
What types of activities have you engaged in with [Field-4]? You can choose multiple answers. - What types of activities have you engaged in with [Field-4]? You can choose multiple answers. - Selected Choice
What types of activities have you engaged in with [Field-5]? You can choose multiple answers. - What types of activities have you engaged in with [Field-5]? You can choose multiple answers. - Selected Choice
etc.

I want it to become this:
Code: 
What types of activities have you engaged in with [Field-3]
What types of activities have you engaged in with [Field-4]
What types of activities have you engaged in with [Field-5]
etc.

So I need to remove this:
Code: 
? You can choose multiple answers. - What types of activities have you engaged in with[*]? You can choose multiple answers. - Selected Choice

My Function
Code: 
Function ReplaceN(ByVal str1 As Variant, strFind As String, strReplace As String, N As Long, Optional Count As Long) As String
Dim i As Long, j As Long
Dim strM As String

  strM = str1
  If Count <= 0 Then Count = 1
    For i = 1 To N - 1
      j = InStr(1, strM, strFind)
      strM = Mid(strM, j + Len(strFind), Len(strM))
    Next i
  If N <= 0 Then
    ReplaceN = str1
  Else
    ReplaceN = Mid(str1, 1, Len(str1) - Len(strM)) & Replace(strM, strFind, strReplace, Start:=1, Count:=Count)
  End If

End Function

I am using it like this:
Code: 
Sub ReplaceNthInstance()
Dim ws As Worksheet
Dim outArray As Variant
Dim i As Long, LR As Long

Set ws = ThisWorkbook.Sheets("Z")

With ws.Range("E:E")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        outArray = .value
        For i = 1 To .Rows.Count
            outArray(i, 1) = ReplaceN(.Cells(i, 1).value, "[*]", vbNullString, 1)
        Next i
        .value = outArray
    End With
End With
End Sub
 
Last edited:
Not sure why you need nth occurrence.

Part before first "?" is identical to your desired output.

Which can be easily dealt with Split function using "?" as delimiter and reading first element of resulting array.

No need for replace operation.

I'd recommend uploading sample workbook (before the operation) and also another sheet indicating what the end result should look like.
 
Chihiro, thanks for the response, I think my example was a poor one here is (hopefully) a better one

Example of possible data

HAVE
Code: 
abc [stuff1] ttt nn [stuff1]sss DDD lll [stuff1]sss zzz  rrr [stuff1]sss
abc [stuff2] ttt nn [stuff2]sss DDD lll [stuff2]sss zzz  rrr [stuff2]sss
abc [stuff3] tttnn  [stuff3]sss DDD lll [stuff3]sss zzz  rrr [stuff3]sss
abc [stuff4] ttt nn [stuff4]sss DDD lll [stuff4]sss zzz  rrr [stuff4]sss
abc [stuff5] ttt nn [stuff5]sss] DDD lll [stuff5]sss zzz  rrr [stuff5]sss
abc [stuff6] ttt nn [stuff6]sss DDD lll [stuff6]sss zzz  rrr [stuff6]sss
abc [stuff7] ttt nn [stuff7]sss DDD lll [stuff7]sss zzz  rrr [stuff7]sss
abc [stuff8] ttt nn [stuff8]sss DDD lll [stuff8]sss zzz  rrr [stuff8]sss

WANT
Code: 
abc [stuff1] ttt nn [stuff1]sss DDD lll QQQQ zzz  rrr [stuff1]sss
abc [stuff2] ttt nn [stuff2]sss DDD lll QQQQ zzz  rrr [stuff2]sss
abc [stuff3] ttt nn [stuff3]sss DDD lll QQQQ zzz  rrr [stuff3]sss
abc [stuff4] ttt nn [stuff4]sss DDD lll QQQQ zzz  rrr [stuff4]sss
abc [stuff5] ttt nn [stuff5]sss DDD lll QQQQ zzz  rrr [stuff5]sss
abc [stuff6] ttt nn [stuff6]sss DDD lll QQQQ zzz  rrr [stuff6]sss
abc [stuff7] ttt nn [stuff7]sss DDD lll QQQQ zzz  rrr [stuff7]sss
abc [stuff8] ttt nn [stuff8]sss DDD lll QQQQ zzz  rrr [stuff8]sss

Replace the 3rd instance of "[stuff1]sss"

I hope this helps explain better what I am after?

Also, I uploaded a file
 

Attachments

  • Book1.xlsm
    8.7 KB · Views: 2
Personally I prefer to do it like below. Using RegEx pattern matching instead of wild card search.

Code: 
Public Function RegExReplace(ostr As String, findpattern As String, repstr As String, inst As Integer)

With CreateObject("VBScript.RegExp")
    .Pattern = findpattern
    .Global = True
        If .Test(ostr) Then
            y = .Execute(ostr)(inst)
            x = Split(ostr, y, inst + 1)
            For i = 0 To UBound(x)
                Z = IIf(Len(Z) = 0, x(i) & y, IIf(i = inst - 1, Z & x(i) & repstr, Z & x(i)))
            Next
            RegExReplace = Z
        Else
            RegExReplace = ostr
        End If
End With
End Function

You'd use it like below.
Code: 
=RegExReplace(A2,"(\[stuff.+?\].{3})","QQQQ",2)

See attached.

Edit: Oh, I just realized it works with your sample, but I forgot to add one additional operation. Let me rethink logic.
 

Attachments

  • Book1.xlsm
    15.9 KB · Views: 2
Last edited:
Here's amended code.
Code: 
Public Function RegExReplace(ostr As String, findpattern As String, repstr As String, inst As Integer)

With CreateObject("VBScript.RegExp")
    .Pattern = findpattern
    .Global = True
        If .Test(ostr) Then
            y = .Execute(ostr)(inst - 1)
            x = Split(ostr, y, inst + 1)
            Debug.Print y
            Debug.Print UBound(x)
            For i = 0 To UBound(x)
                s = IIf(Len(s) = 0, x(i) & y, IIf(i = inst - 1, Z & x(i) & repstr, Z & x(i)))
                Z = IIf(Len(Z) = 0, x(i) & y, IIf(i = inst - 1, Z & x(i) & repstr, Z & x(i)))
            Next
            RegExReplace = Z
        Else
            RegExReplace = ostr
        End If
End With
End Function

And the formula using slightly more explicit pattern.
=RegExReplace(A2,"(\[stuff.{1,3}\]sss)","QQQQ",2)
 
Chihiro, thanks for the help

Can you help me understand this
RegExReplace(A2,"(\[stuff.{1,3}\]sss)","QQQQ",2)

What does the {1,3} mean and the 2

Thanks
 
Hi !

A more beginner way for square data :​
Code: 
Sub Demo1()
    Const C = "[stuff1]sss"
      Dim L&, P&, R&, V
    With Cells(1).CurrentRegion.Columns(1)
            V = .Value
        For R = 1 To 2:  P = InStr(P + 1, V(2, 1), C):  Next
         If P = 0 Then Beep: Exit Sub
            L = P + Len(C)
        For R = 2 To UBound(V)
            V(R, 1) = Left(V(R, 1), P - 1) & "QQQQ" & Mid(V(R, 1), L)
        Next
            .Value = V
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
You must log in or register to reply here.
Back
Top