删除前导数字、句点和空格的 Excel VBA 正则表达式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13234031/
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
Excel VBA Regular Expression to Remove Leading Number, Period, and Space?
提问by brettdj
I have a sheet that I use an Excel VBA macro to automatically fill all worksheet page headers (NOT column headers) with relevant information on a button click. This macro uses wSheet.Name to populate the header title. However, wSheet.Name often contains leading numbers, periods, and spaces that I don't want to appear in the header.
我有一张工作表，我使用 Excel VBA 宏自动填充所有工作表页眉（不是列标题），并在单击按钮时使用相关信息。此宏使用 wSheet.Name 填充标题标题。但是， wSheet.Name 通常包含我不想出现在标题中的前导数字、句点和空格。
Note the following sample worksheet names:
请注意以下示例工作表名称：
Cover Page
1a. Test Page
1b. Sample Page
2. Another Test Page
3. Yet Another Test Page
4. Almost the Last Example Page
998. Last Example Page
I would like to remove these leading numbers, periods, and spaces using a regular expression, yet I'm unsure how to code it using VBA in Excel. I would like it to be as flexible as possible. Here is an example of how I would like the worksheet names to appear:
我想使用正则表达式删除这些前导数字、句点和空格，但我不确定如何在 Excel 中使用 VBA 对其进行编码。我希望它尽可能灵活。这是我希望如何显示工作表名称的示例：
Cover Page
Test Page
Sample Page
Another Test Page
Yet Another Test Page
Almost the Last Example Page
Last Example Page
Here is my existing code that populates the headers:
这是我现有的填充标题的代码：
Sub FillHeaders()
'
' Auto_Fill_Project_Name Macro
'
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Name <> "Cover Page" Then
wSheet.PageSetup.CenterHeader = _
"&16&KFF0000" & ActiveSheet.Range("J1") & "&10&K000000 &16" & " " & _
wSheet.Name & Chr(13) & "&10 &11 Revision Date: "
End If
Next wSheet
'
End Sub
How can I modify this to accomplish my goal?
我该如何修改它以实现我的目标？
Thank you!
谢谢！
EDIT -- I have taken the following approach:
编辑——我采取了以下方法：
Function remleaddig(str As String)
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "^\S*\."
str = regEx.Replace(str, "")
remleaddig = Trim(str)
End Function
回答by brettdj
Update: -- Added UDF
更新：--添加 UDF
Function StripChars(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "^.+\.\s+"
.ignorecase = True
StripChars = .Replace(strIn, vbNullString)
End With
End Function
Initial Post`
初始帖子`
I would combine the regexpwith a variant array to reduce code runtime. The code below uses
我会将正则表达式与变体数组结合起来以减少代码运行时间。下面的代码使用
- Late bindging to set up the
regexp - The values in column A of the
ActiveSheetare read into a variable arrayX(Note that the code will handle a 2D range, ie you could useX = Range([a1], Cells(Rows.Count, "B").End(xlUp)).Value2to work on column A and B - The
regexpstrips out the unwanted characters - it will handle more than one space if it is presnt before the text you want to retain - The cleaned text is dumped to the active sheet starting in
C1. Change this line to move the dump - you could make it dump back over the orginal values starting inA1.[c1].Resize(UBound(X, 1), UBound(X, 2)).Value2 = X
- 后期绑定设置
regexp - 的 A 列中的值
ActiveSheet被读入一个变量数组X（请注意，该代码将处理 2D 范围，即您可以用于X = Range([a1], Cells(Rows.Count, "B").End(xlUp)).Value2处理 A 和 B 列 - 该
regexp带出来的不想要的字符-之前要保留文本将处理超过一个空间，如果它是presnt - 清理后的文本从
C1. 更改此行以移动转储 - 您可以使其转储回从A1.[c1].Resize(UBound(X, 1), UBound(X, 2)).Value2 = X
Code
代码
Sub QuickUpdate()
Dim X()
Dim objRegex As Object
Dim lngRow As Long
Dim lngCol As Long
X = Range([a1], Cells(Rows.Count, "A").End(xlUp)).Value2
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "^.+\.\s+"
.ignorecase = True
For lngRow = 1 To UBound(X, 1)
For lngCol = 1 To UBound(X, 2)
X(lngRow, lngCol) = .Replace(X(lngRow, lngCol), vbNullString)
Next lngCol
Next lngRow
End With
[c1].Resize(UBound(X, 1), UBound(X, 2)).Value2 = X
End Sub
回答by A. Webb
Well, you can actually use regular expressions via the RegEx object of VBScript. See https://stackoverflow.com/a/13041710/1756702for an example.
好吧，您实际上可以通过 VBScript 的 RegEx 对象使用正则表达式。有关示例，请参见https://stackoverflow.com/a/13041710/1756702。
回答by Brad
I'll elaborate on my comment.
我将详细说明我的评论。
This required a reference to Microsoft VBScript Regular Expressions 5.5(or whatever the number is on your machine)
这需要参考Microsoft VBScript Regular Expressions 5.5（或您机器上的任何数字）
Dim r As RegExp
Set r = New RegExp
r.Pattern = "^\S*\. "
Dim c As Range, rng As Range
Set rng = Range("A1:A7") ' <-- Set this to your range
For Each c In rng
c.Value = r.Replace(c.Value, "")
Next c
If all that text is in one cell then change it to
如果所有文本都在一个单元格中，则将其更改为
Dim r As RegExp
Set r = New RegExp
r.Pattern = "^\S*\. "
r.MultiLine = True
r.Global = True
Dim c As Range
Set c = Range("J1")
c.Value = r.Replace(c.Value, "") '<--or place the result where ever you want
The regex explained
正则表达式解释
"^This says that the pattern specified must start at the beginning of the string. we don't want to match numbers that exist in the middle of your header title.\SThis matches everything except white space ([^a-zA-Z0-9]). i.e. it will match letters and numbers.*This matches 0 or more of the previous pattern\.This matches a period (period must be escaped with a\because the.itself means it wants to match 1 or more of the previous pattern."This matches a single space.
"^这表示指定的模式必须从字符串的开头开始。我们不想匹配标题中间存在的数字。\S这匹配除空格 ([^a-zA-Z0-9]) 之外的所有内容。即它将匹配字母和数字。*这匹配 0 个或多个先前的模式\.这匹配一个句点（句点必须用 a 转义，\因为它.本身意味着它想要匹配一个或多个先前的模式。"这匹配单个空格。
