LaVOZs

The World’s Largest Online Community for Developers

'; vba - How to replace Parts of an input String with RegEx? - LavOzs.Com

Im working on a VBA-Project at the moment, and part of the project is to process input Data. My Problem is, the input string is not the same every time.

At the moment i have the problem with Values like 1 000.00. They have a Blank instead of a thousands separator. It looks like this:

2020.02.10 13:40:14 106353221 GOLD sell 0.08 1 571.80 1 571.26 1 534.00 2020.02.11 14:23:42 1 571.43 0.00  0.26  2.90

As you can see there are values like for example 1 561.80. Now im looking for a solution to delete this space. The Problem is I cannot use Replace, because the next step ist splitting the String into an array. And there i need the spaces for correct splitting...

My Idea was to use Regular Expressions, what i came up with so far:

    Public Function repla(myString As String, Muster As String)
    
    Dim regex As New RegExp
        
    regex.Global = True
    regex.Pattern = Muster
    Set Fundstellen = regex.Execute(myString)
    
    Ersatz = "Test"
    
    repla = regex.Replace(myString, Ersatz)
    
    End Function

    Debug.Print repla(TextboxText, "[0-9] [0-9][0-9][0-9].[0-9][0-9]")

This give me the following output:

2020.02.10 13:40:1Test221 GOLD sell 0.08 Test Test Test 2020.02.11 14:23:42 Test 0.00  0.26  2.90

As you can see there are two Problems right now:

  • Its also replacing numbers which it shouldnt, like 106353221
  • So far i didnt manage to replace the part of the string with it self, but without the " ".

Does anyone have an Idea how i could get this to work?? Im quite stuck at the moment.

Try, The part enclosed in parentheses corresponds to the submatch.

(submatch(1))(submatch(2))(submatch(3))

Public Function repla(myString As String, Muster As String)

    Dim regex As New RegExp
    Dim Ersatz As String
    
    regex.Global = True
    regex.Pattern = Muster
    Set Fundstellen = regex.Execute(myString)
    
    Ersatz = "$1$3" 'submatch(1) and subamatch(3) : submatch(2)-> space so, Change it to the rest except space.
    
    repla = regex.Replace(myString, Ersatz)

End Function
Sub test()
    Dim TextboxText As String
    TextboxText = "2020.02.10 13:40:14 106353221 GOLD sell 0.08 1 571.80 1 571.26 1 534.00 2020.02.11 14:23:42 1 571.43 0.00  0.26  2.90"
    Debug.Print repla(TextboxText, "( [0-9])( )([0-9][0-9][0-9].[0-9][0-9])")
End Sub

Actually i found a way to replace the part of the string with it self, but without the " ".

But i still have the problem that the number 106353221 also gets replaced..

The function looks like this at the moment (string is hardcoded just to test)

Public Sub repla()

strtext = "2020.02.10 13:40:14 106353221 GOLD sell 0.08 1 571.80 1 571.26 1 534.00 2020.02.11 14:23:42 1 571.43 0.00  0.26  2.90"

Debug.Print strtext

Set objRegex = CreateObject("vbscript.regexp")
    strIn = strtext
    With objRegex
        .Pattern = "([0-9] [0-9][0-9][0-9]).([0-9][0-9])"
        .Global = False
        .IgnoreCase = True
        .MultiLine = False
        Do While .test(strIn)
            Set objRegMC = .Execute(strIn)
            For Each objRegM In objRegMC
            
                test1 = objRegM
                test1 = Replace(objRegM, " ", "")
                strIn = .Replace(strIn, test1)

            Next
        Loop
    End With
    
    Debug.Print strIn

End Sub

Output before processing looks like this:

2020.02.10 13:40:14 106353221 GOLD sell 0.08 1 571.80 1 571.26 1 534.00 2020.02.11 14:23:42 1 571.43 0.00 0.26 2.90

Output after processing looks like this:

2020.02.10 13:40:14106353221 GOLD sell 0.08 1571.80 1571.26 1534.00 2020.02.11 14:23:42 1571.43 0.00 0.26 2.90

The Problem is still this "106353221" part of the string.. Anyone got an idea??

Related