How to split a cell of many numbers by group of 3-

问题:

As above image I have a column which consists many numbers separated by comma. I need to split those numbers by group of 3-3 in each next cell.

回答1:

So, brutal but does work:

=LEFT(A1,FIND(",",A1,FIND(",",A1,FIND(",",A1,1)+1)+1)-1)

You can play with MID() and RIGHT() to get others....

See

回答2:

Give a try:

Option Explicit

Sub test()

    Dim str As String
    Dim Appears As Long, i As Long, Times As Long, LastColumn As Long, StartPoint As Long, EndPoint As Long

    With ThisWorkbook.Worksheets("Sheet1")

        str = .Range("A1").Value & ","

        Times = 0

        StartPoint = 1

        For i = 1 To Len(str)

            If Mid(str, i, 1) = "," Then

                Times = Times + 1

                If CDbl(Times / 3) = Round(CDbl(Times / 3)) Then

                    EndPoint = i

                    LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

                    .Cells(1, LastColumn + 1).Value = Mid(str, StartPoint, EndPoint - StartPoint)

                    StartPoint = EndPoint + 2

                End If

            End If

        Next i

    End With

End Sub