How to replace Parts of an input String with RegEx?

Question

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 something like \D\d \d{3}.\d{2)\D learn.microsoft.com/en-us/previous-versions/windows/… — Tim Williams
– Tim Williams, Commented Jun 30, 2020 at 6:41

Dy.Lee · Accepted Answer · 2020-06-30 07:02:03Z

1

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

edited Jun 30, 2020 at 7:02

answered Jun 30, 2020 at 6:48

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LucaS Over a year ago

Almost perfect, thank you for your Help! But it has the same problem like my solution... the Output string comes 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". Do you see the Problem? For some reason the space before 106353221 is getting deleted... Any Ideas on this?

Dy.Lee Over a year ago

@LucaS, add space ( [0-9])( )```` , i edited answer.

LucaS · Accepted Answer · 2020-06-30 06:44:29Z

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??

Collectives™ on Stack Overflow

How to replace Parts of an input String with RegEx?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related