Csdrhrt

Suppose in Excel cell A1 there's content "a,b,c,d,e",
is there a way to write an array formula, so that in B1:B5 will show the split result, i.e. B1 = a, B2 = b, B3 = c, B4 = d, B5 = e?

Without formula (my favorite) :

Select A1 > Data > Text to Column > Delimited : "," as limiter > Finish ( Choose replace for now..)

Select the result ( 5 cells) > Ctrl+C > r-click on B1, choose transpose. done.

With formula :

B1  --->  =MID(A1,1,FIND(",",$A$1,1)-1)

B2  --->  =MID($A$1,FIND(",",$A$1,1)+1,FIND(",",$A$1,FIND(",",$A$1,1)+1)-FIND(",",$A$1,1)-1)

B3  --->  =MID($A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1)-FIND(",",$A$1,FIND(",",$A$1,1)+1)-1)

B4  --->  =MID($A$1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1)+1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1)+1)-FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1)-1)

B5  --->  =MID($A$1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1)+1)+1,LEN(A1)-FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,FIND(",",$A$1,1)+1)+1)+1))

key : find the n-th "," in the string.. as "markers" for mid() function.

Hope it helps.

I would like to suggest a MACRO, which is the best & fastest method, to split Comma separated values from Cell to Rows.

This MACRO will takes data in column A and put the results in column B.

Sub SplitData()

    Dim src As Range

    Dim result As Variant

    For Each src In Range("A:A").SpecialCells(xlCellTypeConstants)

        result = Split(src, ",")

        With Cells(Rows.Count, 2).End(xlUp)

            Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)

        End With

    Next src

End Sub

N.B.

• At the active sheet press Alt+F11 to open the VB Editor then Copy & Paste this code as Standard Module, finally RUN it.


• 
  Range("A:A") for Column A and (Rows.Count.2) for Column B are editable.