Split Single cell Data into Multiple cells

  • Hello All,


    Please help me get a solution in an excel, I have a column (C), in every cell from C2 onward there are data in the below format:
    [TABLE="width: 341"]

    [tr]


    [td]

    BlackBerry Device Service
    Managing Devices
    Managing Users
    Settings
    Universal Device Service
    Working with Log Files
    Working with Profiles


    Now I need to get each line of these cell in different cell in column (E).



    Please assist


    Regards

    [/td]


    [/tr]


    [/TABLE]

  • Re: Split Single cell Data into Multiple cells


    Please advise the result of this formula:


    =CODE(MID(C2,26,1))


    where C2 contains that exact information you show in the example.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Split Single cell Data into Multiple cells


    it gives a result 10, actually I am trying to split the cell data in different cell
    like in the cell (C2) data says


    BlackBerry Device Service
    Managing Devices
    Managing Users
    Settings
    Universal Device Service
    Working with Log Files
    Working with Profiles


    now I need to get "BlackBerry Device Service" in a differnt cell and same for other

  • Re: Split Single cell Data into Multiple cells


    Ok try this in E2:


    [COLOR="#0000FF"]=TRIM(MID(SUBSTITUTE($C2,CHAR(10),REPT(" ",100)),1+(100*(COLUMNS($C2:C2)-1)),100))[/COLOR]


    copied across as far as needed, then you can copy down for C3, C4, etc....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Split Single cell Data into Multiple cells


    Thanks for your help its working the way it si required, but some of the line gets spilt in different like "Working with Log Files" gets spilt into 2 column, that is "working with" and in other column "Log Files" and also some other.

  • Re: Split Single cell Data into Multiple cells


    Since the length of the string in C2 is 136, the choice of 100 is here insufficient.


    Replace all instances of this value in the formula given to LEN($C2), which will always be sufficient.


    Regards

  • Re: Split Single cell Data into Multiple cells


    Good call XOR LX. Thanks.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Split Single cell Data into Multiple cells


    Thank You all for the wonderful solution, I was able to do perfectly,


    could you please assit if I change from columns to Rows will my formula remain same? if I change the formula from COLUMNS to ROWS ?


    Please assist

  • Re: Split Single cell Data into Multiple cells


    Almost...



    [COLOR="#0000FF"]=TRIM(MID(SUBSTITUTE($C2,CHAR(10),REPT(" ",LEN($C2))),1+(LEN(C$2)*([COLOR="#FF0000"]ROWS[/COLOR](C[COLOR="#FF0000"]$[/COLOR]2:C2)-1)),LEN($C2)))[/COLOR]


    Notice the $ is moved from before the C to before the 2 in order to "lock" the row when copying down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!