Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Split the contents from single cell into multiple cell in excel 2010

  1. #1
    Junior Member
    Join Date
    Apr 2015
    Posts
    8
    Rep Power
    0

    Lightbulb Split the contents from single cell into multiple cell in excel 2010

    I have to Split the contents from single cell into multiple cell in excel 2010.

    Input:

    Cell1 contains 3 sentences,

    Step1: Am santhosh from Coimbatore.
    Step2: Am working as testing Engineer.
    Step3: I need solution for this.


    I need to cplit the above cell1 into 3 cells below (Cell2, cell3, cell4)


    Cell1:
    Step1: Am santhosh from Coimbatore.

    Cell2:
    Step2: Am working as testing Engineer.

    Cell3:
    Step3: I need solution for this.

    Please someone help me. I need to do the same for lot of cells in the document. Any short cut or macros to do this. pls help me on this.
    email: san.rathinapuri@gmail.com

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Select the cell or cells that you want to split apart and then run this macro...
    Code:
    Sub SplitSelectedCellsDown()
      Dim Cell As Range, Sentences() As String
      For Each Cell In Selection
        Sentences = Split(Cell.Value & vbLf, vbLf)
        Cell.Offset(1).Resize(UBound(Sentences)) = Application.Transpose(Sentences)
      Next
    End Sub

  3. #3
    Junior Member
    Join Date
    Apr 2015
    Posts
    8
    Rep Power
    0
    Hi Rick,

    Thanks a lot for your solution.

    It reduces the 50% of my work, still i have to modify few things as like below,

    Input: Cell1 contains 3 sentences,

    Step1: Am santhosh from Coimbatore.
    Step2: Am working as testing Engineer.
    Step3: I need solution for this.


    I need to split the above cell1 into 3 cells below (Cell2, cell3, cell4). Your code works here but..
    Before executing the macro given by you, i need to create 3 empty cells below that to get the proper output. Otherwise its overwriting the existing content.

    Any solution to create the 3 empty cells first (Based on the number of sentences available in cell1) and then seperate the contents as like your macro.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    This revised macro should work for you...
    Code:
    Sub SplitSelectedCellsDown()
      Dim Cell As Range, Sentences() As String
      For Each Cell In Selection
        Sentences = Split(Cell.Value & vbLf, vbLf)
        Cell.Offset(1).Resize(UBound(Sentences)).Insert xlShiftDown
        Cell.Offset(1).Resize(UBound(Sentences)) = Application.Transpose(Sentences)
      Next
    End Sub

  5. #5
    Junior Member
    Join Date
    Apr 2015
    Posts
    8
    Rep Power
    0
    Hi Rick,

    Thanks a lot for the code. Its working good for single cell selection.

    Am facing small issue hile running this macro.

    Input:

    Cell A1 contains 3 sentences,
    Step1: Am santhosh from Coimbatore.
    Step2: Am working as testing Engineer.
    Step3: I need solution for this.

    Cell A2 contains 3 sentences,
    Step1: Am santhosh from Chennai.
    Step2: Am working as development Engineer.
    Step3: I need solution for this also.

    If i select this both cell at a time and run the macro, its going on running without any end point. i need to force close this macro to stop. Can you please please provide solution for this.

  6. #6
    Junior Member
    Join Date
    Apr 2015
    Posts
    8
    Rep Power
    0
    Hi Rick,

    Can you please share your email id. I will send the excel sheet, it will be better to understand my requirements clearly.

    Thanks,
    Santhosh

  7. #7
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    In the reply window, click on the Go Advanced button. Look for the manage attachments button and follow the wizard to attach your workbook onto this forum.

  8. #8
    Junior Member
    Join Date
    Apr 2015
    Posts
    8
    Rep Power
    0

    Please find attached document

    Hi Rick,

    please refer the attached document with input and output.

    The cell alignment and everthing i have mentioned exactly. please help me to sort out this.

    I need to reduce my time using macros.


    Thanks,
    Santhosh
    Attached Files Attached Files

  9. #9
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    For starters, you have merged cells. VBA does not like and will not usually work with merged cells. You will need to redesign your workbook so that there are no merged cells. Additionally, in your post you indicated that there are only 3 lines of data in each cell. This is obviously not the case. You have multiple rows of data (more than the three) indicated in your problem. It is difficult to provide you with an accurate solution if you don't provide accurate information on your issue.

  10. #10
    Junior Member
    Join Date
    Apr 2015
    Posts
    8
    Rep Power
    0
    ok thanks for the reply Alansidman.

    Before run this macro i will try to unmerge all the cells.

    yes, there will be multiple sentences also in single cell, its not limited. It may vary from one cell to another cell.

    Any solution for my document after unmerging..?

Similar Threads

  1. Replies: 3
    Last Post: 02-11-2014, 08:31 PM
  2. Replies: 3
    Last Post: 11-22-2013, 08:22 AM
  3. Replies: 1
    Last Post: 08-23-2013, 05:19 AM
  4. Replies: 5
    Last Post: 01-12-2013, 02:49 AM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •