excel - 如何根据特殊字符拆分字符串（多次）

Question

我正在尝试根据与符号 (&) 拆分字符串，删除所有与符号并将每个部分分隔为列。

字符串的数量每次都不同。

示例文本：

我的输出：

我需要的：

For i = 2 To 7
    fullname = Cells(i, 1).Value
    commaposition = InStr(fullname, "&")

    Cells(i, 2).Value = Left(fullname, commaposition - 2)

    For x = 2 To 7
        fullname = Cells(i, 1).Value
        commaposition = InStr(fullname, "&")

        Cells(i, 3).Value = Mid(fullname, commaposition + 2)
        Cells(x, 3).Value = Mid(fullname, commaposition + 2)

        For y = 2 To 7
            fullname = Cells(i, 1).Value
            commaposition = InStr(fullname, "&")

            Cells(i, 4).Value = Mid(fullname, commaposition + 2)
            Cells(x, 4).Value = Mid(fullname, commaposition + 2)

        Next y
    Next x
Next i

标签： excelvbastring

Answer 1

Another option (other then @Storax's method) would be to use Regular Expressions which could account for more then just an ampersand.

Option Explicit
Public Sub FindNames()
    Dim rng As Range
    Dim j As Long
    Dim c, Match

    ' Update for your range
    With ActiveSheet
        Set rng = .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1))
    End With

    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "\w+"

        For Each c In rng
            j = 0
            If .test(c.Value2) Then
                For Each Match In .Execute(c.Value2)
                    j = j + 1
                    c.Offset(0, j).Value2 = Match
                Next Match
            End If
        Next c
    End With
End Sub