我们有大约3500个文件,这些文件的文件名需要手动清除特殊字符,如括号、冒号、分号、逗号等。
我有一个文本文件,我把它转储到excel中,我想创建一个列,如果文件名中包含特殊字符,就标记它进行修改。伪代码公式是
=IF (cellname contains [^a-zA-z_-0-9], then "1", else "0")
如果行中包含A-Z、0-9、-或 \以外的字符,不管大小写,都要进行标记。
有谁知道一些可能适合我的方法?如果有快速简单的方法,我不愿意写代码和大量的if语句。
我们有大约3500个文件,这些文件的文件名需要手动清除特殊字符,如括号、冒号、分号、逗号等。
我有一个文本文件,我把它转储到excel中,我想创建一个列,如果文件名中包含特殊字符,就标记它进行修改。伪代码公式是
=IF (cellname contains [^a-zA-z_-0-9], then "1", else "0")
如果行中包含A-Z、0-9、-或 \以外的字符,不管大小写,都要进行标记。
有谁知道一些可能适合我的方法?如果有快速简单的方法,我不愿意写代码和大量的if语句。
解决方案找到了!
没有代码?但它是如此的短小精悍,简单漂亮,而且……:(
你的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_-]
使用类似于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 的具体内容
必须启用宏才能工作!
这里有一个条件格式化解决方案,它将标记带有特殊字符的记录。
只需对你的数据应用一个新的条件格式化规则,该规则使用下面的(极长的)公式,其中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的单元格都会被标记。
我使用了一种不同的方法来查找特殊字符。我为每一个允许的字符创建了新的列,然后使用这样的公式来计算该允许字符在每一行条目(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)上进行排序,找到负值,从而找到需要修正的列。