CODEWITHSUNDEEP
vbaexpression
Terry
Terry

Reputation: 61

Please suggest reg express for every nth occurrence of a character in a string in vba

At the very outset, let me confess. I am not a developer nor do I have any technical background. However, I have learned a bit of coding. I want to a regular expression for finding every nth position of a character. For example, google, yahoo, rediff, facebook, cnn, pinterest, gmail. I want to find every third occurrence of the comma in the string and replace it with a semicolon. This is for a excel vba macro I am working on. For now, I am trying to loop through it and then replace. If the data is large, the macro fails. Would appreciate your reply. Thanks a ton in advance.

Here is what I am doing: 

Option Explicit

Sub reg()

Dim regx As RegExp
Set regx = New RegExp
Dim allMatches As Object

Dim contents As String
Dim contents2 As String
contents = "hello, wow, true, false, why, try, cry, haha"
contents = "contents1" & contents
regx.pattern = ",{4}"
regx.Global = True
regx.IgnoreCase = False
Set allMatches = regx.Execute(contents)

contents2 = regx.Replace(contents, ";")


MsgBox contents2

End Sub

I get the data from all selected cells. Join it. Add semicolon (an indicator for the row end) at every fourth comma found. Please suggest if there is a better way to do it as I am new to this:

Here is what I have done currently by looping through array. I want to avoid this. 

Function insertColon(sInputString As String) As Variant


Dim data3 As String
Dim sFind As String
Dim sReplacewith As String
Dim result As String
'Dim i As Integer
Dim Counter As Integer

sFind = ","
sReplacewith = ";"
data3 = sInputString

' MsgBox = data3 ' Dim J As Integer Application.Volatile FindN = 0 'Dim i As Integer ' i = 1

Counter = 4

' MsgBox Len(data3)

While InStr(Counter, sInputString, sFind)

    FindN = InStr(Counter, sInputString, sFind)

    data3 = Application.Substitute(data3, sFind, sReplacewith, Counter)

    Counter = Counter + 3

' MsgBox "loop" & i ' ' i = i + 1

Wend

Upvotes: 1

Views: 1014

Answers (1)

george
george

Reputation: 3221

If I understood you properly then all your code could be summarized to a few lines

        Dim sText As String
        sText = "hello, wow, true, false, why, try, cry, haha, huh, muh"

        For p = 3 To Len(sText) Step 2
          sText = WorksheetFunction.Substitute(sText, ",", ";", p)
        Next p

       MsgBox sText
       'Output
       'hello, wow, true; false, why, try; cry, haha, huh; muh

Test it and let me know whether it fails. If not don't forget to accept the answer.

Upvotes: 2

Related Questions