Suppose in A1 cell I have some comma separated letters.

content(A1 cell) = A, W, L, F

I would like to the following result:

content(A2 cell) = A

content(A3 cell) = W

content(A4 cell) = L

content(A5 cell) = F

However if I type in A1 cell the following:

content(A1 cell) = S, E, U, F, H, R, T

I would like to be the result:

content(A2 cell) = S

content(A3 cell) = E

content(A4 cell) = U

content(A5 cell) = F

content(A6 cell) = H

content(A7 cell) = R

content(A8 cell) = T

Is there a function which will do this job? For example I would like to a function formula in B1 cell, which checks the content of A1 cell and its outputs will be the above results.
Any help appreciated.

formula

=INDEX(MID(SUBSTITUTE($A$1,", ",""),ROW($A$1:INDIRECT("a"&LEN(SUBSTITUTE($A$1,", ","")))),1),ROWS($A$1:A1))

Split Cell Into Rows

Functions do not work in the way you described the possible functionality in your question, but here is a 'similar' VBA solution using the Worksheet Change Event.

Highlights

• In Worksheet_Change you can choose (provide) the Split Cell Range
  Address containing the delimited data (cStrCell) and the Split
  Delimiter (cStrDel).


• When changing the data in the Split Cell Range, the solution will
  copy the delimited data below the Split Cell Range into the
  Split Data Range and delete all data below.


• The change of any cell in the Split Data Range is not possible.

Copy the following code to the desired sheet code window e.g. Sheet1(Code).

The Code

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)



    Const cStrCell As String = "A1"   ' Split Cell Range Address

    Const cStrDel As String = ","     ' Split Delimiter



    Application.EnableEvents = False



    ' Check if changed cell range is contained in Split Range.

    If Not Intersect(Target, Range(cStrCell).Resize(UBound(Split( _

            Range(cStrCell), cStrDel)) + 2)) Is Nothing Then



        On Error GoTo ProcedureExit



        ' Check if changed cell range address

        ' is equal to Split Cell Range Address.

        If Target.Address = Range(cStrCell).Address Then

            ' Clear contents of data below Split Range.

            Range(Range(cStrCell).Offset(1), Cells(Rows.Count, _

                    Range(cStrCell).Column).End(xlUp)).ClearContents

        End If



        ' Fill Split Range with Split Data

        ' i.e. copy Split Data below Split Cell Range.

        SplitToRows Range(cStrCell), cStrDel



    End If



ProcedureExit:

    Application.EnableEvents = True



End Sub



Sub SplitToRows(SplitCellRange As Range, Optional Delimiter As String = ",")



    Dim vntS As Variant   ' Source Array

    Dim vntT As Variant   ' Target Array

    Dim i As Long         ' Arrays Row Counter



    ' Split first cell in SplitCellRange by Delimiter into Source Array.

    vntS = Split(SplitCellRange.Cells(1, 1), Delimiter)



    ' Resize 2D one-based Target Array.

    ReDim vntT(1 To UBound(vntS) + 1, 1 To 1)



    ' Trim and copy from Source to Target Array.

    For i = 0 To UBound(vntS)

        vntT(i + 1, 1) = Trim(vntS(i))

    Next



    ' Copy Target Array to Target Range below SplitCellRange.

    SplitCellRange.Cells(1, 1).Offset(1).Resize(UBound(vntT)) = vntT



End Sub