How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9666
Experience:  Vast experience in Excel programming and business solutions
Type Your Programming Question Here...
John D is online now
A new question is answered every 9 seconds

How to split Excel cell contents into separate cell rows in

This answer was rated:

How to split Excel cell contents into separate cell rows in different sheet

I have a challenge to split the cell contents of several cells in one Excel sheet into separate row cells in the same column in a different sheet based on the <ALT> <ENTER> delimiter within a cell and each separate cell.

For example, Sheet 1 contains many cells in different rows and columns that each have textual data with some on separate lines in the cell (using EXCEL ALT-ENTER) that needs to be analyzed individually using separate rows in a column in the next sheet. Can you help me?
See example below;

Sheet 1
       C1              C2              C3
R1 Function 1   Function 3 Function 5
      Function 2   Function 4

R2 Function 6   Function 7 Function 9
                         Function 8

What I want in Sheet 2 is as follows

R1 Function 1
R2 Function 2
R3 Function 3
R4 Function 4
R5 Function 5
R6 Function 6
R7 Function 7
R8 Function 8
R8 Function 9



Here you go:




Hope this is ok. Let me know if you have any questions.


John D and other Programming Specialists are ready to help you
Customer: replied 8 years ago.
That was an incredibly quick response! This is what I am looking for. Could I trouble you to provide a commented version of the macro/script so I can understand what you are doing so I can customize it? I also have to make this work for a variable number of source columns and rows with the delimited functions in the cells (from the first sheet) and stop after the last row that contains any functions. I also have to be able to exclude (skip) performing the split on cells that contain known values (such as "none" or "n/c" or are empty. Can you help me with this?

Great! Glad it turned out to be what you were looking for. Thanks for the feedback and bonus.


Ok, I will modify the macro to allow for variable columns and exclude certain cells. These will be defined at the beginning of the macro so the user can enter the desired values. I will also add comments in the macro code.


Will get back to you as soon as I am done.


Customer: replied 8 years ago.

Thanks John. I appreciate your expertise and assistance.

Okay, here is the modified file. I think with the comments the changes are self explanatory.




Hope this helps. Let me know if you have any questions




John D and other Programming Specialists are ready to help you