🔖 exceltools

Usage

  • 启用 VBA (*.xlsm 文件中有效)

    • 在 Excel 中：文件 --> 选项 --> 自定义功能区 勾选 开发工具
    excel-enable-vba.png

  • 启用 Microsoft VBScript Regular Expressions5.5 模块

    • 打开 Visual Basic 编辑器：开发工具 --> Visual Basic
    • 在 Visual Basic 编辑器中打开：工具 --> 引用
    • 在弹出的对话框中勾选 Microsoft VBScript Regular Expressions 5.5
    excel-enable-vba-regex.png

  • 导入正则表达式的 VBA 脚本

    • 在 Visual Basic 编辑器中打开 插入模块（文件选项卡的正下方） --> 模块

    • 在弹出的对话框中粘贴如下脚本：

      regex.vb 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
          Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
          Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
          Dim replaceNumber As Integer
      

          With inputRegexObj
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = matchPattern
          End With
          With outputRegexObj
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = "\$(\d+)"
          End With
          With outReplaceRegexObj
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
          End With
      

          Set inputMatches = inputRegexObj.Execute(strInput)
          If inputMatches.Count = 0 Then
              regex = False
          Else
              Set replaceMatches = outputRegexObj.Execute(outputPattern)
              For Each replaceMatch In replaceMatches
                  replaceNumber = replaceMatch.SubMatches(0)
                  outReplaceRegexObj.Pattern = "\$" & replaceNumber
      

                  If replaceNumber = 0 Then
                      outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                  Else
                      If replaceNumber > inputMatches(0).SubMatches.Count Then
                          'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                          regex = CVErr(xlErrValue)
                          Exit Function
                      Else
                          outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                      End If
                  End If
              Next
              regex = outputPattern
          End If
      End Function

    • 保存，然后关闭各种对话框

    excel-import-regex-script.png

  • 使用

    直接选中单元格，然后在上面输入公式即可，语法如下：

    1
    regex(<original_string>, <regex_pattern>, <replace_string>)
    • <original_string> 原始字符串，可以使用 Excel 中的单元格引用，如 E4
    • <regex_pattern> 正则表达式，无需转义，如："(\d+)\.(\d+)\.(\d+)"
    • <replace_string> 替换的字符串，如："$1年$2月$3日"
    excel-regex-demo1.png

Related

© 2017-2025 光和尘有花满渚、有酒盈瓯

Comments