Пример макроса: Регулярные выражения

Function MatchesToString(theMatches As Object) As String '' for e30 RegEx
    Dim Match As Object
    Dim s As String
    For Each Match In theMatches
        s = s & Match.value & vbNewLine
    Next
    MatchesToString = s
End Function

Sub RegexTesting()                            '' e30
    Dim regexOne As Object, theMatches As Object
    Set regexOne = CreateObject("VBScript.RegExp")
    
    ' check whether a pattern matches a sequence in the input string.
    '   The result is True if a match is found
    Range("A2") = "f....a"
    regexOne.Pattern = Range("A2")
    Range("B2") = "000111fjo88a8"
    Range("C2") = regexOne.Test(Range("B2"))
    
    ' use the Replace method to replace the first instance of a matching pattern in a string
    '   or all the instances of a matching pattern in a string.
    '   If Global is set to False, then only the first instance is replaced
    regexOne.Global = False
    Range("A3") = "This is the number"
    regexOne.Pattern = Range("A3")
    Range("B3") = "This is the number 718901"
    Range("C3") = regexOne.Replace(Range("B3"), "That is the new number")
           
    ' replace only the number portion of the string used above
    regexOne.Global = False
    Range("A4") = "[^\D]+"
    regexOne.Pattern = Range("A4")
    Range("B4") = Range("B3")
    Range("C4") = regexOne.Replace(Range("B4"), "777192")
                                 
    ' replace every instance of a certain pattern in a string
    regexOne.Global = True
    Range("A5") = "\W\A\d+C\W"
    regexOne.Pattern = Range("A5")
    Range("B5") = "ABC-A1289C-ABC-A1289C-ABC"
    Range("C5") = regexOne.Replace(Range("B5"), "IJK")
                                     
    ' use the Execute method to match one or all instances of a pattern within a string
    regexOne.Global = True
    regexOne.IgnoreCase = True
    Range("A6") = "A.C"
    regexOne.Pattern = Range("A6")
    Range("B6") = "ABC-A1289C-ADC-A1289C-AJC"
    Set theMatches = regexOne.Execute(Range("B6"))
    Range("C6") = MatchesToString(theMatches)
                                                                  
    regexOne.Global = False
    regexOne.IgnoreCase = True
    Range("A7") = "\-\A.C\-"
    regexOne.Pattern = Range("A7")
    Range("B7") = "ABC-A1289C-ADC-A1289C-AEC"
    Set theMatches = regexOne.Execute(Range("B7"))
    Range("C7") = MatchesToString(theMatches)
                                     
    Columns("A:C").AutoFit
End Sub

(function()
{
    // Write value into cell by address 
    function CellValue(address,value) {
        var oSheet = Api.GetActiveSheet();
        oSheet.GetRange(address).SetValue(value);
        return oSheet.GetRange(address).GetValue();
    }
    
    function AutofitColumns(columns) {
        Api.GetActiveSheet().GetCols(columns).AutoFit(true,true);
    }
    
    // check whether a pattern matches a sequence in the input string.
    //   The result is True if a match is found
    var pattern = CellValue("A2", "f....a");
    var str = CellValue("B2", "000111fjo88a8");
    // "g" = RegExp.Global in VBA. 
    // If flag "g" is not exists, then only the first instance is replaced
    var regex = new RegExp(pattern, "g");
    CellValue("C2", regex.test(str));
    
    // use the Replace method to replace the first instance of 
    //   a matching pattern in a string or all the instances.
    pattern = CellValue("A3", "This is the number");
    str = CellValue("B3", "This is the number 718901");
    CellValue("C3", str.replace(pattern, "That is the new number"));
    
    // replace only the number portion of the string used above
    pattern = CellValue("A4", "\\d+");
    str = CellValue("B4", "This is the number 718901");
    regex = new RegExp(pattern, "g");
    CellValue("C4", str.replace(regex, "777192"));
    
    // replace every instance of a certain pattern in a string
    pattern = CellValue("A5", "\\W\\A\\d+C\\W");
    str = CellValue("B5", "ABC-A1289C-ABC-A1289C-ABC");
    regex = new RegExp(pattern, "g");
    CellValue("C5", str.replace(regex, "IJK"));
    
    // use the Execute method to match one or all instances of a pattern within a string
    pattern = CellValue("A6", "A.C");
    str = CellValue("B6", "ABC-A1289C-ADC-A1289C-AJC");
    // "i" = RegExp.IgnoreCase in VBA. Search without case-sensitive
    regex = new RegExp(pattern, "gi");
    CellValue("C6", str.match(regex).join(', '));

    pattern = CellValue("A7", "\-\A.C\-");
    str = CellValue("B7", "ABC-A1289C-ADC-A1289C-AEC");
    // "i" = RegExp.IgnoreCase in VBA. Search without case-sensitive
    regex = new RegExp(pattern, "i");
    CellValue("C7", str.match(regex).join(', '));

    AutofitColumns("A:C");    
})();