我们有大约 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) 非常容易安装和使用:
- ALT-F11 打开 VBE 窗口
- ALT-I ALT-M 打开新模块
- 粘贴内容并关闭 VBE 窗口
如果您保存工作簿,UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本,则必须将文件保存为 .xlsm 而不是 .xlsx
要删除 UDF:
- 调出如上所示的 VBE 窗口
- 清除代码
- 关闭 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 码是否超出允许的字符值。不幸的是,允许的字符代码并不都是连续的,所以这就是公式必须使用SUMPRODUCT
s 的总和的原因。该公式返回坏字符的数量。任何返回值大于 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)进行排序以查找负值,这使我找到了需要更正的列。