0

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?

Community
  • 1
  • 1
Marco M
  • 59
  • 11
  • 1
    the third parameter is the limit `Split(string, ")", 2)` https://msdn.microsoft.com/en-us/library/6x627e5f. RegEx is another option https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Slai Jun 20 '17 at 00: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 Jun 20 '17 at 01:15

1 Answers1

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?

Slai
  • 22,144
  • 5
  • 45
  • 53