使用正则表达式拆分列中单元格的内容 [英] Split the content of cells in a column using regex

问题描述

这是我直到现在的代码，实际上删除单元格中的所有内容，但是数字。看起来像这样：

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

它工作正常...现在，我想做的是将单元格的内容拆分成3个单元格：一个与数字之间的一切，一个与数字一个，一个与什么是数字之后。我有这样的列：

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:

<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"/>

我想得到这样的东西：（在NP ++中，正则表达式将是（<那么在VBA中，它是[...]（[0-9] {1,3}）（。>），然后替换为\1\t\2\t\3更复杂，我想请求帮助这个...希望有人会有一个想法：）

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 :)

<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"/>

现在我的问题不在于正则表达式本身（我与正则表达式相当）比VBA代码允许我真正拆分单元格，并将3部分发送到3个不同的单元格中。

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...

推荐答案

你正在与正则表达式非常接近，我不得不做一个小的修改。

You are pretty close with the Regex, I had to make a small modification.

首先，请确保将Microsoft VBScript正则表达式5.5的引用添加到您的VBA模块（请参阅此链接如何做到这一点）。

First, make sure you add the reference to "Microsoft VBScript Regular Expressions 5.5" as to your VBA module (see this link for how to do that).

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 = "$1"

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

---

我的输入是在列B和输出以C，D& E

---

My input is in column B and the output is shown in C, D & E

这篇关于使用正则表达式拆分列中单元格的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！