Macro to split the text in the cell to different cells ( as per enclosure )

[mvel_sky]

Dear Sirs,

I need a help to write a macro, to split the text in a cell to different cells.

I have enclosed the attachment for more clarification.

Please do the needful.

Thanks
Regards
M.Velmurugan

[jaslake]

Hi mvel_sky
Try this

Option Explicit
Sub Split_Text()
    Dim NR As Long
    Dim sp As Variant

    sp = Split(Range("C5"), "*")
    sp = RemItem(sp, 0)

    Range("D5").Resize(UBound(sp) + 1, 1).Value = Application.WorksheetFunction.Transpose(sp)
End Sub

Function RemItem(sp As Variant, n As Long) As Variant
' From http://excel.bigresource.com/Track/excel-X6MbfKzM/
    Dim i As Long
    For i = n + 1 To UBound(sp) - LBound(sp)
        sp(i - 1) = sp(i)
    Next i
    ReDim Preserve sp(LBound(sp) To UBound(sp) - 1)
    RemItem = sp
End Function