如何在Microsoft Excel中使用正则表达式（正则表达式）在单元格和循环中

6 回答

• 35

  要直接在Excel公式中使用正则表达式，以下UDF（用户定义的函数）可能会有所帮助 . 它或多或少直接将正则表达式功能公开为excel函数 .

  它是如何工作的

  它需要2-3个参数 .

  • 要使用正则表达式的文本 .

  • 正则表达式 .

  • 指定结果外观的格式字符串 . 它可以包含 $0 ， $1 ， $2 等 . $0 是整个匹配， $1 和up对应于正则表达式中的相应匹配组 . 默认为 $0 .

  一些例子

  提取电子邮件地址：

  =regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
  =regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")
  

  结果： some@email.com

  提取几个子串：

  =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
  

  结果： E-Mail: some@email.com, Name: Peter Gordon

  将单个单元格中的组合字符串拆分为多个单元格中的组件：

  =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
  =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)
  

  结果： Peter Gordon some@email.com ...

  如何使用

  要使用此UDF，请执行以下操作（大致基于this Microsoft page . 它们有一些很好的附加信息！）：

  • 在启用宏的文件（'.xlsm'）中的Excel中，按 ALT+F11 打开Microsoft Visual Basic for Applications编辑器 .

  • 添加对正则表达式库的VBA引用（从Portland Runners++ answer无耻地复制）：

  • 点击工具 - >参考文献（请原谅德语截图）
    

  • 在列表中找到Microsoft VBScript Regular Expressions 5.5并勾选旁边的复选框 .

  • 单击“确定” .

  • 单击“插入模块” . 如果为模块指定了不同的名称，请确保模块与下面的UDF名称不同（例如，命名模块 Regex ，函数 regex 会导致#NAME！错误） .

  

  • 在中间的大文本窗口中插入以下内容：

  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
  

  • 保存并关闭“Microsoft Visual Basic for Applications编辑器”窗口 .

  回复于 2024-04-20T11:12:44+08:00
• 166

  这是一个 regex_subst() 函数 . 例子：

  =regex_subst("watermellon", "[aeiou]", "")
  ---> wtrmlln
  =regex_subst("watermellon", "[^aeiou]", "")
  ---> aeeo
  

  这是简化的代码（无论如何，对我来说简单） . 我无法弄清楚如何构建一个合适的输出模式使用上面的工作像我的例子：

  Function regex_subst( _
       strInput As String _
     , matchPattern As String _
     , Optional ByVal replacePattern As String = "" _
  ) As Variant
      Dim inputRegexObj As New VBScript_RegExp_55.RegExp
  
      With inputRegexObj
          .Global = True
          .MultiLine = True
          .IgnoreCase = False
          .Pattern = matchPattern
      End With
  
      regex_subst = inputRegexObj.Replace(strInput, replacePattern)
  End Function
  

  回复于 2024-04-20T11:12:44+08:00
• 18

  我需要将它用作单元格函数（如 SUM 或 VLOOKUP ）并发现它很容易：

  • 确保您位于启用宏的Excel文件中（另存为xlsm） .

  • 打开开发人员工具Alt F11

  • 与其他答案一样，添加Microsoft VBScript正则表达式5.5

  • 在工作簿或其自己的模块中创建以下函数：

  Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
      Dim regex As New VBScript_RegExp_55.RegExp
      Dim strInput As String
  
      strInput = myRange.Value
  
      With regex
          .Global = True
          .MultiLine = True
          .IgnoreCase = False
          .Pattern = matchPattern
      End With
  
      REGPLACE = regex.Replace(strInput, outputPattern)
  
  End Function
  

  • 然后你可以在单元格中使用 =REGPLACE(B1, "(\w) (\d+)", "$1$2") （例如："A 243"至"A243"）

  回复于 2024-04-20T11:12:44+08:00
• 1

  Regular expressions用于模式匹配 .

  要在Excel中使用，请执行以下步骤：

  Step 1 ：将VBA引用添加到"Microsoft VBScript Regular Expressions 5.5"

  • 选择"Developer"标签（I don't have this tab what do I do?）

  • 从'Code'功能区部分选择"Visual Basic"图标

  • 在"Microsoft Visual Basic for Applications"窗口中从顶部菜单中选择"Tools" .

  • 选择"References"

  • 选中"Microsoft VBScript Regular Expressions 5.5"旁边的框以包含在工作簿中 .

  • 点击"OK"

  Step 2 ：定义您的模式

  基本定义：

  - 范围 .

  • 例如 a-z 匹配a到z的小写字母

  • 例如 0-5 匹配0到5之间的任何数字

  [] 恰好匹配这些括号内的一个对象 .

  • 例如 [a] 匹配字母a

  • 例如 [abc] 匹配单个字母，可以是a，b或c

  • 例如 [a-z] 匹配字母表中的任何单个小写字母 .

  () 为返回目的分组不同的匹配项 . 见下面的例子 .

  {} 用于在其之前定义的模式的重复副本的乘数 .

  • 例如 [a]{2} 匹配两个连续的小写字母a： aa

  • 例如 [a]{1,3} 匹配至少一个和最多三个小写字母 a ， aa ， aaa

  + 匹配之前定义的模式中的至少一个或多个 .

  • 例如 a+ 将匹配连续的 a ， aa ， aaa ，依此类推

  ? 匹配前面定义的零或一个模式 .

  • 例如模式可能存在也可能不存在，但只能匹配一次 .

  • 例如 [a-z]? 匹配空字符串或任何单个小写字母 .

  * 匹配之前定义的模式的零个或多个 . - 例如可能存在或不存在的模式的通配符 . - 例如 [a-z]* 匹配空字符串或字符串小写字母 .

  . 匹配除换行符之外的任何字符 \n

  • 例如 a. 匹配以a开头并以 \n 以外的任何内容结尾的双字符串

  | OR运算符

  • 例如 a|b 表示可以匹配 a 或 b .

  • 例如 red|white|orange 恰好匹配其中一种颜色 .

  ^ NOT运算符

  • 例如 [^0-9] 字符不能包含数字

  • 例如 [^aA] 字符不能是小写 a 或大写 A

  \ 转义后面的特殊字符（覆盖上述行为）

  • 例如 \. ，014343， \( ， \? ， \$ ， \^

  ---

  锚定模式：

  ^ 匹配必须在字符串的开头发生

  • 例如 ^a 第一个字符必须是小写字母 a

  • 例如 ^[0-9] 第一个字符必须是数字 .

  $ 匹配必须发生在字符串的末尾

  • 例如 a$ 最后一个字符必须是小写字母 a

  ---

  优先表：

  Order  Name                Representation
  1      Parentheses         ( )
  2      Multipliers         ? + * {m,n} {m, n}?
  3      Sequence & Anchors  abc ^ $
  4      Alternation         |
  

  ---

  预定义字符缩写：

  abr    same as       meaning
  \d     [0-9]         Any single digit
  \D     [^0-9]        Any single character that's not a digit
  \w     [a-zA-Z0-9_]  Any word character
  \W     [^a-zA-Z0-9_] Any non-word character
  \s     [ \r\t\n\f]   Any space character
  \S     [^ \r\t\n\f]  Any non-space character
  \n     [\n]          New line
  

  ---

  Example 1 ：以宏的形式运行

  以下示例宏查看单元格 A1 中的值，以查看前1个或2个字符是否为数字 . 如果是这样，它们将被删除，并显示其余的字符串 . 如果没有，则会出现一个框，告诉您没有找到匹配项 . 12abc 的单元格 A1 将返回 abc ， 1abc 的值将返回 abc ， abc123 的值将返回"Not Matched"，因为数字不在字符串的开头 .

  Private Sub simpleRegex()
      Dim strPattern As String: strPattern = "^[0-9]{1,2}"
      Dim strReplace As String: strReplace = ""
      Dim regEx As New RegExp
      Dim strInput As String
      Dim Myrange As Range
  
      Set Myrange = ActiveSheet.Range("A1")
  
      If strPattern <> "" Then
          strInput = Myrange.Value
  
          With regEx
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = strPattern
          End With
  
          If regEx.Test(strInput) Then
              MsgBox (regEx.Replace(strInput, strReplace))
          Else
              MsgBox ("Not matched")
          End If
      End If
  End Sub
  

  ---

  Example 2 ：作为单元格函数运行

  此示例与示例1相同，但设置为作为单元内函数运行 . 要使用，请将代码更改为：

  Function simpleCellRegex(Myrange As Range) As String
      Dim regEx As New RegExp
      Dim strPattern As String
      Dim strInput As String
      Dim strReplace As String
      Dim strOutput As String
  
  
      strPattern = "^[0-9]{1,3}"
  
      If strPattern <> "" Then
          strInput = Myrange.Value
          strReplace = ""
  
          With regEx
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = strPattern
          End With
  
          If regEx.test(strInput) Then
              simpleCellRegex = regEx.Replace(strInput, strReplace)
          Else
              simpleCellRegex = "Not matched"
          End If
      End If
  End Function
  

  将字符串（"12abc"）放在单元格 A1 中 . 在单元格 B1 中输入此公式 =simpleCellRegex(A1) ，结果将为"abc" .

  

  ---

  Example 3 ：循环范围

  此示例与示例1相同，但循环遍历一系列单元格 .

  Private Sub simpleRegex()
      Dim strPattern As String: strPattern = "^[0-9]{1,2}"
      Dim strReplace As String: strReplace = ""
      Dim regEx As New RegExp
      Dim strInput As String
      Dim Myrange As Range
  
      Set Myrange = ActiveSheet.Range("A1:A5")
  
      For Each cell In Myrange
          If strPattern <> "" Then
              strInput = cell.Value
  
              With regEx
                  .Global = True
                  .MultiLine = True
                  .IgnoreCase = False
                  .Pattern = strPattern
              End With
  
              If regEx.Test(strInput) Then
                  MsgBox (regEx.Replace(strInput, strReplace))
              Else
                  MsgBox ("Not matched")
              End If
          End If
      Next
  End Sub
  

  ---

  Example 4 ：拆分不同的模式

  此示例循环一个范围（ A1 ， A2 ＆ A3 ），并查找以三位数字开头的字符串，后跟单个字母字符，然后是4位数字 . 输出通过使用 () 将模式匹配拆分为相邻单元 . $1 表示第一组 () 中匹配的第一个模式 .

  Private Sub splitUpRegexPattern()
      Dim regEx As New RegExp
      Dim strPattern As String
      Dim strInput As String
      Dim Myrange As Range
  
      Set Myrange = ActiveSheet.Range("A1:A3")
  
      For Each C In Myrange
          strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
  
          If strPattern <> "" Then
              strInput = C.Value
  
              With regEx
                  .Global = True
                  .MultiLine = True
                  .IgnoreCase = False
                  .Pattern = strPattern
              End With
  
              If regEx.test(strInput) Then
                  C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                  C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                  C.Offset(0, 3) = regEx.Replace(strInput, "$3")
              Else
                  C.Offset(0, 1) = "(Not matched)"
              End If
          End If
      Next
  End Sub
  

  结果：

  

  ---

  Additional Pattern Examples

  String   Regex Pattern                  Explanation
  a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
  a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceeding alpha character
  a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
  a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters
  
  </i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit
  

  回复于 2024-04-20T11:12:44+08:00
• 1

  这是我的尝试：

  Function RegParse(ByVal pattern As String, ByVal html As String)
      Dim regex   As RegExp
      Set regex = New RegExp
  
      With regex
          .IgnoreCase = True  'ignoring cases while regex engine performs the search.
          .pattern = pattern  'declaring regex pattern.
          .Global = False     'restricting regex to find only first match.
  
          If .Test(html) Then         'Testing if the pattern matches or not
              mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
              RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
          Else
              RegParse = "#N/A"
          End If
  
      End With
  End Function
  

  回复于 2024-04-20T11:12:44+08:00
• 772

  为那些匆忙的人扩展patszim的answer .

  • 打开Excel工作簿 .

  • Alt F11打开VBA / Macros窗口 .

  • 在 Tools 下添加对正则表达式的引用然后 References

  • 并选择 Microsoft VBScript Regular Expression 5.5

  • 插入一个新模块（代码需要驻留在模块中，否则它不起作用） .

  • 在新插入的模块中，

  • 添加以下代码：

  Function RegxFunc(strInput As String, regexPattern As String) As String
      Dim regEx As New RegExp
      With regEx
          .Global = True
          .MultiLine = True
          .IgnoreCase = False
          .pattern = regexPattern
      End With
  
      If regEx.Test(strInput) Then
          Set matches = regEx.Execute(strInput)
          RegxFunc = matches(0).Value
      Else
          RegxFunc = "not matched"
      End If
  End Function
  

  • 正则表达式模式放在其中一个单元格中，并在其上使用 absolute referencing . 函数将与其创建的工作簿绑定 .
    如果需要在不同的工作簿中使用它，请将该函数存储在 Personal.XLSB 中

  回复于 2024-04-20T11:12:44+08:00