Split multi-line cell text into multiple cells while maintaining neighboring cell contents for each entry

  • Hello, all! First post for me. And I'm somewhat inexperienced on forums in general.

    I had a similar issue to another post on this forum which is why I chose to sign up on OzGrid versus StackOverflow. However, I need some additional assist if possible.

    Split Multi line Cell text Into Multiple Rows in range

    What I need is for if there is only one line in the cell (ie there is no Chr(10)), for it to still show up in my recreated list.


    Also, just for a bonus, so more important to focus on the above, but I also have not used this particular method of moving on the next row before. If the data that needed to be split was in Column "C", how would I tell it to look there within the above script?

    Below are some of the other "LastRow" variations I have tried in order to specify what column it looks in rather than just "A". I just wanted to demonstrate that I am attempting/experimenting/researching before just asking someone to solve my problems.

    Thank you, thank you, for any and all support.


  • Are the colours just for clarity of your explanation, or do you need the desired result to be coloured?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Just for clarity. Color is not needed. Primarily just need that blank entry in the result from where there was only one line in the original data to not be blank.

    Thank you,


  • Try this

    I have shown where in the code you can change it to suit the column immediately prior to the column with the multi line data

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I very much appreciate the time invested and creativity in problem solving. There seems to be a miscommunication as to what is my end goal. I apologize.

    Notably, this code chops the first character off of the data. I did not request this in my post either in word or image, though I notice that my uploaded workbook does show this as the desired difference. I had added those letters to the workbook for ease of identifying what moved where while I was working and neglected to notice that I had saved that change before uploading when prompted.

    It also removes the header and tampers with columns outside of A and B (or whichever ones that will be used in my larger workbook). If I need to do this process in another sheet and call it from there to avoid this, that's ok. Just a bonus if it can be all in one place with my other data.

    It looks like it will accept cells with even more lines than were in the example so that's stellar. Some of the user entries could be dozens of lines.

    I also need to have it preserve the original data and put the new list in an assigned column similar to my original code.

    Here is a more simplistic presentation of what my goal is. Colors and borders do not need to be preserved and are again there for clarity in presenting the idea of original data and desired positioning.

    I'll work through the code you provided some more, but request a little more assist.

    Here's the updated workbook: Split Test.xlsm

    Thank you!

  • The file you attached is the original file not the updated one

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • There are multiple sheets in the above .xlsm whereas the original .xlsx just had the one.

    The most recent example is titled "Sheet1" for ease. The desired results are shown on sheet "End Goal". The original example is the third sheet.

    I have uploaded the .xlsm again with the first sheet activated and with a new title so to not be mistaken with either of the above files that would've been better practice for me to have donein the first place.

    Split Test FierceMouse.xlsm

    Thank you again for your patience.


  • OK my mistake re the multiple sheets.

    Try the attached file. I have added a button, click it to run the code.

    Note that the new code is in a Standard Module, the ThisWorkbook Object Module should only contain Workbook Event code.

    Code assigned to the button is:

    Change the code where indicated to suit your actual workbook.

    In this example Column B is the column immediately prior to the column that has the multiline data hence the variable lCol is given a value of 2, and the sheet name is "Sheet1".

  • This is actually how it would reflect in my greater workbook. The two empty columns will already be there, ready for the split lines to be there. If columns were to be inserted it would change references to data to the right and I'd like to prevent that. They will not necessarily be right next to it, I would like to be able to specify.

    Sheet1 is the original data, all columns constant, the empty columns will be there, and all of it will be more or less nested between data that I do not want shifted up/down/left/right. If it takes multiple steps, it doesn't really matter to me how many columns are in there, just so that

    The "End Goal" sheet is identical to "Sheet1", just with the data manually put there how I would like the code to put it there.

    Here it is as requested, the only major difference is that I put other columns in there to signify that the original data and the resulting data will not be neighboring columns: Split Test Example Extended.xlsm

    Here is my bigger workbook: Carbaryl Search Terms Snip.xlsm. It is full of bugs and other potentially distracting problems/details that I will will get to at a later date, but then you can see the more or less the full context. In this workbook, the data with multiple lines is in column "S". The destination column for the split data is "W". The tag that is repeated for each line is in "R" to be in "V". This code will be in the sub "RelChemsSubmitButton".

    Again, thank you for all you insight on this project.


  • Thanks for your actual workbook example, always easier to work with a realistic example.

    The VBA is password protected, what is the password? you can PM it to me if you prefer.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Here is your actual file sample with the modified SplitData code and a button to run it.

    You worksheet said "All Terms, Split, Cleaned" so I included code to trim and clean all spilt lines.

    Modified code:

  • You're welcome, and thank you for the Likes.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hey, KjBox. I was trying to change this up a touch in order for it to maintain the previous three columns vs the previous one.

    These are the changes I've made so far in this workbook: Search Terms Tool.xlsm, thinking I could reverse engineer your code and understand how the array is being manipulated, but I think that doing it this way is messing with the 4th column too many times. And of course the adjustments I made cause bugs. I imagine there is a simple line that lets me preserve the three cells to the left rather than preserving row after row and trying to overlay them.

    I had gotten it to behave at one point though not preserve correctly.

    This is what would be stellar. No color, borders, or dimensions, etc. need to be preserved.


  • I think I may have come up with a remedial workaround. I TextJoined the 3 columns to the left, did your code, and then split the column I textJoined.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!