vba 使用正则表达式拆分列中单元格的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22514376/
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
Split the content of cells in a column using regex
提问by Nicoladastra
Here is the code that I had until now, which actually deletes everything in a cell but the numbers. This looked like this:
这是我到现在为止的代码，它实际上删除了单元格中除数字之外的所有内容。这看起来像这样：
Sub myTest()
Dim myCel As Range
Dim i As Integer
i = 0
'copier le contenu de la colonne A dans la colonne B
Columns("A:A").Select
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste
'on supprime tout ce qui n'est pas un nombre
With CreateObject("VBScript.Regexp")
.Global = True
.Pattern = "\D+"
For Each myCel In Range("B1:B900")
myCel.Value = .Replace(myCel.Value, "")
Next
End With
End Sub
It worked alright... Now, what I want to do is actually to split the content of the cells into 3 cells: one with everything that's before the numbers, one with the numbers and one with what is after the number. I have got this kind of column:
它工作得很好......现在，我想要做的实际上是将单元格的内容分成3个单元格：一个是数字之前的所有内容，一个是数字，一个是数字之后的内容。我有这样的专栏：
<g0 t="bold">
</g0>
<g1>
</g1>
<g2>
</g2>
<g3>
</g3>
<g4>
</g4>
<i5 t="lb"/>
<i6 t="lb"/>
<g7>
</g7>
<g8>
</g8>
<i9 t="lb"/>
<i10 t="lb"/>
<i11 t="lb"/>
And I would like to get something like this: (in NP++ the regex would be (<.?)([0-9]{1,3})(.>) and then replace with \1\t\2\t\3. Well, in VBA it's more complex and I wanted to ask for help on this one... hope someone will have an idea :)
我想得到这样的东西：（在 NP++ 中，正则表达式是 (<. ?)([0-9]{1,3})(.>) 然后用 \1\t\2\t 替换\3. 好吧，在 VBA 中它更复杂，我想在这个问题上寻求帮助......希望有人能有一个想法:)
<g 0 t="bold">
</g 0 >
<g 1 >
</g 1 >
<g 2 >
</g 2 >
<g 3 >
</g 3 >
<g 4 >
</g 4 >
<i 5 t="lb"/>
<i 6 t="lb"/>
<g 7 >
</g 7 >
<g 8 >
</g 8 >
<i 9 t="lb"/>
<i 10 t="lb"/>
<i 11 t="lb"/>
Now my question is not so much about the regex itself (I am quite ok with regex) than the VBA code that allows me to really split the cell and send the 3 parts to 3 different cells in a loop...
现在我的问题不是关于正则表达式本身（我对正则表达式很满意）而是 VBA 代码，它允许我真正拆分单元格并将 3 个部分发送到循环中的 3 个不同单元格......
回答by Portland Runner
You are pretty close with the Regex, I had to make a small modification.
你很接近正则表达式，我不得不做一个小的修改。
First, make sure you add the reference to "Microsoft VBScript Regular Expressions 5.5" as to your VBA module (see this linkfor how to do that).
首先，确保将“Microsoft VBScript 正则表达式 5.5”的引用添加到 VBA 模块（请参阅此链接以了解如何执行此操作）。
Private Sub TestRegex()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strRaplace As String
Dim strOutput As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("B1:B19")
For Each C In Myrange
strPattern = "(\<.?.?)([0-9]{1,3})(.*>)"
If strPattern <> "" Then
strInput = C.Value
strReplace = ""
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.Test(strInput) Then
C.Offset(0, 1) = regEx.Replace(strInput, "")
C.Offset(0, 2) = regEx.Replace(strInput, "")
C.Offset(0, 3) = regEx.Replace(strInput, "")
Else
C.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
My input is in column B and the output is shown in C, D & E
我的输入在 B 列中，输出显示在 C、D 和 E 中
回答by Gary's Student
Without Regex, select your cells and:
如果没有正则表达式，请选择您的单元格，然后：
Sub parser101()
Dim r As Range, v As String, L As Long
Dim I As Long, N1 As Long, N2 As Long
N1 = 0
N2 = 0
For Each r In Selection
v = r.Text
L = Len(v)
For I = 1 To L
If IsNumeric(Mid(v, I, 1)) Then
N1 = I
GoTo escape101
End If
Next I
'
escape101:
'
For I = N1 To L
If Not IsNumeric(Mid(v, I, 1)) Then
N2 = I
GoTo escape102
End If
Next I
'
escape102:
'
r.Offset(0, 1) = Mid(v, 1, N1 - 1)
r.Offset(0, 2) = Mid(v, N1, N2 - N1)
r.Offset(0, 3) = Mid(v, N2)
Next r
End Sub
