Excel 是否有公式可以识别单元格中的特殊字符?

Excel 是否有公式可以识别单元格中的特殊字符?

我们有大约 3500 份文档,其文件名需要手动清理以删除括号、冒号、分号、逗号等特殊字符。

我有一个文本文件,我已将其转储到 excel 中,并尝试创建一个列,如果文件名包含特殊字符,则标记该文件名需要修改。伪代码公式如下

=IF (cellname contains [^a-zA-z_-0-9], then "1", else "0")

如果该行包含除 AZ、0-9、- 或 _ 之外的任何字符(无论大小写),则标记该行。

有人知道有什么方法可以帮我吗?if如果有快速简便的方法,我不太愿意编写代码和进行大量声明。

答案1

没有代码?但是它很短小精悍,而且很漂亮... :(

您的 RegEx 模式[^A-Za-z0-9_-]用于删除所有单元格中的所有特殊字符。

Sub RegExReplace()

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True

    RegEx.Pattern = "[^A-Za-z0-9_-]"
    For Each objCell In ActiveSheet.UsedRange.Cells
        objCell.Value = RegEx.Replace(objCell.Value, "")
    Next

End Sub

编辑

这和你最初的问题最接近了。

在此处输入图片描述

第二个代码是带有 2 个参数的用户定义函数=RegExCheck(A1,"[^A-Za-z0-9_-]")。第一个是要检查的单元格。第二个是要检查的 RegEx 模式。如果模式与单元格中的任何字符匹配,它将返回 1,否则返回 0。

你可以像其他普通 Excel 公式一样使用它,只要你先用ALT+打开 VBA 编辑器F11,插入一个新的模块(!)并粘贴下面的代码。

Function RegExCheck(objCell As Range, strPattern As String)

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = strPattern

    If RegEx.Replace(objCell.Value, "") = objCell.Value Then
        RegExCheck = 0
    Else
        RegExCheck = 1
    End If

End Function

对于 RegEx 的新用户,我将解释您的模式:[^A-Za-z0-9_-]

[] stands for a group of expressions
^ is a logical NOT
[^ ] Combine them to get a group of signs which should not be included
A-Z matches every character from A to Z (upper case)
a-z matches every character from a to z (lower case)
0-9 matches every digit
_ matches a _
- matches a - (This sign breaks your pattern if it's at the wrong position)

答案2

使用与 nixda 的代码类似的内容,这里有一个用户定义的函数,如果单元格有特殊字符,它将返回 1。

Public Function IsSpecial(s As String) As Long
    Dim L As Long, LL As Long
    Dim sCh As String
    IsSpecial = 0
    For L = 1 To Len(s)
        sCh = Mid(s, L, 1)
        If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
        Else
            IsSpecial = 1
            Exit Function
        End If
    Next L
End Function

用户定义函数 (UDF) 非常容易安装和使用:

  1. ALT-F11 打开 VBE 窗口
  2. ALT-I ALT-M 打开新模块
  3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿,UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本,则必须将文件保存为 .xlsm 而不是 .xlsx

要删除 UDF:

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

要从 Excel 使用 UDF:

=IsSpecial(A1)

要了解有关宏的更多信息,请参阅:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

有关 UDF 的详细信息

必须启用宏才能使此功能起作用

答案3

这是一个条件格式解决方案,它将用特殊字符标记记录。

只需对您的数据应用一个新的条件格式规则,该规则使用下面的(极长的)公式,其中A1是文件名列中的第一个记录:

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>45))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>95))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>122)*1)

此公式检查每个文件名的每个字符,并确定其 ASCII 码是否超出允许的字符值。不幸的是,允许的字符代码并不都是连续的,所以这就是公式必须使用SUMPRODUCTs 的总和的原因。该公式返回坏字符的数量。任何返回值大于 0 的单元格都会被标记。

例子: 在此处输入图片描述

答案4

我使用了不同的方法来查找特殊字符。我为每个允许的字符创建了新列,然后使用如下公式来计算该允许字符在每个行条目中出现的次数(Z2):

AA2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AA$1,""))
AB2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AB$1,""))
...

然后我计算每行允许的字符数,然后将其与行条目的总长度进行比较。

BE2=LEN(Z2)
BF2=SUM(AA2:BC2)-BE2

最后,我对最后一列(BF2)进行排序以查找负值,这使我找到了需要更正的列。

相关内容