excel - 如何在下一列中的分隔符“：”，“，”之间拆分文本？

Question

我很难弄清楚如何在 VBA 中解决这个问题。我需要在其各自的单元格中输入正确的信息，例如：

例子：

在单元格范围内，名称应该只是第一行的名称 Amanda...

我一直在使用宏，但它不起作用，因为它只适用于 1 个分隔符。知道如何解决这个问题吗？

标签： excelregexvba

Answer 1

试试这个代码

Sub SplitValuesToNextColumns()
Dim rng As Range, cl As Range, str As String
Dim regex As Object, mc As Object

Set regex = CreateObject("VBScript.regexp")
regex.ignorecase = False
regex.Global = True

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select column range" & vbLf & _
        "Macro result will spill in the next 4 columns of the selected range", _
        Title:="Select Range", Default:="B3", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cl In rng
    str = Replace(Replace(Replace(Replace(cl.Value, _
        "1-Name=", "["), _
        ", 2-Last Name=", "]["), _
        ", 3-Address=", "]["), _
        ", 4-Status=", "][") & "]"

    regex.Pattern = "\[[^]]+\]"
    Set mc = regex.Execute(str)
    For i = 0 To mc.Count - 1
        Cells(cl.Row, cl.Offset(, i + 1).Column) = _
                    Mid(mc(i), 2, Len(mc(i)) - 2)
    Next i
Next cl

End Sub

编辑 - 不需要正则表达式

正如@RaymondWu 在下面的评论中所建议的那样。

Sub SplitValuesToNextColumns()
Dim rng As Range, cl As Range, str As String, arr

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select column range" & vbLf & _
        "Macro result will spill in the next 4 columns of the selected range", _
        Title:="Select Range", Default:="B3", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cl In rng
    str = Replace(Replace(Replace(Replace(cl.Value, _
        "1-Name=", ""), _
        ", 2-Last Name=", "|"), _
        ", 3-Address=", "|"), _
        ", 4-Status=", "|")
    arr = Split(str, "|")
        cl.Offset(, 1).Resize(1, UBound(arr) + 1) = arr
Next cl

End Sub