如何通过宏将上述单个单元格地址值拆分为多个单元格值

Question

如何通过宏将下面的单个单元格值拆分为多个单元格值？

所以这...

A1 = MISS FirstName LastName***95*SPECIAL ROAD**LONDON SW92**SW929AB

...应按如下方式拆分，

B1 = MISS
C1 = FirstName 
D1 = LastName
E1 = 95 SPECIAL ROAD
F1 = LONDON SW92
G1 = SW929AB

有人可以帮忙吗？

Answer 1

根据您提供的演示，以及您编写的事实“如何通过宏将上述单个单元格值拆分为多个单元格值，如下所示”，这正是这样做的

Sub Button1_Click()
Dim address() As String
address() = Split(Range("A1").Value, "**")

Dim col As Integer
col = 66

    For Each Item In address

        If (col = 66) Then

            For Each Name In Split(Item, " ")
                Range(Chr(col) & 1).Value = Trim(Name)
                col = col + 1
            Next Name

        Else

        Dim newValue As String
        newValue = Replace(Item, "*", " ")

            If (newValue <> "") Then
                Range(Chr(col) & 1).Value = Trim(newValue)
                col = col + 1
            End If

        End If

    Next Item

End Sub