vba 如何在Excel中使用正则表达式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19259688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use regular expressions in Excel?
提问by Aleksei Nikolaevich
I have a column of values that can be numbers, letter, characters or both. I need to sieve that column into a new column with the following rules:
我有一列值可以是数字、字母、字符或两者兼而有之。我需要使用以下规则将该列筛选到一个新列中：
1) if a cell contains numbers then concatenate "89"
1) 如果单元格包含数字，则连接“89”
2) if a cell contains numbers and hyphens, trim the hyphens and concatenate 89
2) 如果单元格包含数字和连字符，则修剪连字符并连接 89
3) if a cell contains letters or other spec characters, say string
3) 如果单元格包含字母或其他规范字符，请说字符串
column resultingColumn
1234 123489
12-34hk string
&23412 string
99-9 99989
34-4 34489
I tried but its not as easy as it seemed
我试过了，但它并不像看起来那么容易
Function SC(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
//i am not sure how to list the rules here
.ignorecase = True
SC = .Replace(strIn, vbNullString)
End With
End Function
回答by Gary's Student
You do not require Regular Expressions. Consider:
您不需要正则表达式。考虑：
Function aleksei(vIn As Variant) As Variant
Dim z As Variant
z = Replace(vIn, "-", "")
If IsNumeric(z) Then
aleksei = z & "89"
Else
aleksei = "string"
End If
End Function
Edit#1:
编辑#1：
Based on Nirk's comment, if the decimal point is to be excluded as part of a "number, then use instead:
根据 Nirk 的评论，如果小数点要作为“数字的一部分”被排除，则改用：
Function aleksei(vIn As Variant) As Variant
Dim z As Variant, zz As Variant
z = Replace(vIn, "-", "")
zz = Replace(z, ".", "A")
If IsNumeric(zz) Then
aleksei = z & "89"
Else
aleksei = "string"
End If
End Function
回答by SheetJS
You don't need a regular expression or even VBA for this: set B2 to the following formula and fill down:
为此，您不需要正则表达式甚至 VBA：将 B2 设置为以下公式并填写：
=IF(SUM(LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0,"-"},"")))-10*LEN(A2)=0,SUBSTITUTE(A2,"-","")&"89","string")
What this does is calculate the lengths if we remove each of the characters in the class [0-9\-]from the text. If there are no other characters, then we will have removed each of the characters once, so the total sum of lengths of the strings is 10 times the original string. If there are extraneous characters, they won't be deleted and so the sum will exceed the threshold.
如果我们[0-9\-]从文本中删除类中的每个字符，它的作用是计算长度。如果没有其他字符，那么我们将每个字符都删除一次，因此字符串的总长度总和是原始字符串的 10 倍。如果有多余的字符，它们不会被删除，因此总和将超过阈值。
