How to replace values in a string with values getting edited in between

Question

I want to edit a string that contains the following:

From

WEEK = '2'
Date_Number(3,4,5,6,7,8)
AND
Allowance='100'
AND
((End_Date<'2017-09-17 00:00:00'
AND

Saturday_Name)

to

WEEK = '2'
Date_Number(22,25,27,28,29)
AND
Allowance='100'
AND
((End_Date<'2017-09-17 00:00:00'
AND

Saturday_Name)

I am already thinking of using split string using multiple delimiters ('Date_Number(' and ')') but the problem would be the other ')' will be also be splitted into an array. Is there a way that I could get or even edit the value between it?

the third parameter is the limit Split(string, ")", 2) msdn.microsoft.com/en-us/library/6x627e5f. RegEx is another option stackoverflow.com/questions/22542834/… — Slai
– Slai, Commented Jun 20, 2017 at 0:16
Your answer works! Although the problems is I have to edit other similar text but different placement on the string. The Date_Number might be placed on the 5th row or any row for that matter. The limit will be non-compliant by that sequence — Marco M
– Marco M, Commented Jun 20, 2017 at 1:15

Slai · Accepted Answer · 2017-06-20 01:30:30Z

1

Not tested, but you can try this pattern Date_Number\(.*\)

Sub test()
    Dim str As String
    str = "WEEK = '2' ..."

    With CreateObject("VBScript.RegExp")
        .Pattern = "Date_Number\(.*\)"
        str = .Replace(str, "Date_Number(22,25,27,28,29)")
    End With
End Sub

How do I get regex support in excel via a function, or custom function?

edited Jun 20, 2017 at 1:30

answered Jun 20, 2017 at 0:28

Slai

23k5 gold badges49 silver badges55 bronze badges

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

2 Comments

Marco M Over a year ago

How do you execute with statements? It gives me an error saying that "activex component can't create object vba"

Slai Over a year ago

@MarcoM it should be in a Sub or Function

Collectives™ on Stack Overflow

How to replace values in a string with values getting edited in between

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related