如何在Excel中执行正则表达式? [英] How do you execute a regular expression in Excel?

问题描述

我正在尝试使用以下表达式在Excel数据中定位文本模式.目标是在找到文本后再将其删除.

I am trying to use the following expression to locate a pattern of text in my Excel data. The goal is to then remove the text once it is located.

/.([0-9] + []?x []?[0-9] + []?dpi)./i

帮助！

推荐答案

我创建了一个用户定义函数来运行正则表达式搜索并在单元格中显示最终匹配项.

I have made a User Defined Function to run a regex search and display the final match in the cell.

=udfRegEx([Cell you want to find the expression],[Cell with the regular expression you want to use])

您需要打开Visual Basic编辑器，并将以下代码放入模块中:

You need to open the Visual Basic editor and put the following code into a Module:

Function udfRegEx(CellLocation As Range, RegPattern As String)

Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
Dim OutPutStr As String

    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = RegPattern
    End With

        OutPutStr = ""
        Set RegMatchCollection = RegEx.Execute(CellLocation.Value)
        For Each RegMatch In RegMatchCollection
            OutPutStr = OutPutStr & RegMatch
        Next
        udfRegEx = OutPutStr

    Set RegMatchCollection = Nothing
    Set RegEx = Nothing
    Set Myrange = Nothing

End Function

也不要忘记添加Microsoft VBScript正则表达式5.5的引用

Also don't forget to add the Reference for Microsoft VBScript Regular Expressions 5.5

这篇关于如何在Excel中执行正则表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！