2013-10-16 13:34:39 +0000 2013-10-16 13:34:39 +0000
13
13

有没有一个Excel公式可以识别单元格中的特殊字符？

我们有大约3500个文件，这些文件的文件名需要手动清除特殊字符，如括号、冒号、分号、逗号等。

我有一个文本文件，我把它转储到excel中，我想创建一个列，如果文件名中包含特殊字符，就标记它进行修改。伪代码公式是

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

如果行中包含A-Z、0-9、-或 \以外的字符，不管大小写，都要进行标记。

有谁知道一些可能适合我的方法？如果有快速简单的方法，我不愿意写代码和大量的if语句。
来源

答案 (4)

19
19
19
2013-10-16 14:26:04 +0000

没有代码？但它是如此的短小精悍，简单漂亮，而且……:(

你的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

编辑

这是我能得到的最接近你原来问题的地方。

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

第二段代码是一个用户定义的函数=RegExCheck(A1,"[^A-Za-z0-9_-]")，有两个参数。第一个是要检查的单元格，第二个是RegEx模式。第二个参数是要检查的RegEx模式。

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

[] 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)

对于RegEx的新用户，我将解释你的模式：[^A-Za-z0-9_-]

来源
7
7
7
2013-10-16 15:31:19 +0000

使用类似于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 的具体内容

必须启用宏才能工作!
来源
2
2
2
2013-10-16 21:05:57 +0000

这里有一个条件格式化解决方案，它将标记带有特殊字符的记录。

只需对你的数据应用一个新的条件格式化规则，该规则使用下面的（极长的）公式，其中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的单元格都会被标记。

示例：

。
来源
1
1
1
2016-06-20 21:36:00 +0000

我使用了一种不同的方法来查找特殊字符。我为每一个允许的字符创建了新的列，然后使用这样的公式来计算该允许字符在每一行条目（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)上进行排序，找到负值，从而找到需要修正的列。
来源