Split Cell Data to New Rows & Copy Cells Containing Other Data to New Rows

[ex2012]

Hello everyone,

I do have a problem with splitting cell data to new rows and then copying other cells that contain other data to the new rows. The solutions given in a number of similar threads do not work for me. So when I tried to modify the macro from the thread below I got the message that "subscript out of range". I am unfamiliar with VBA. I tried unsuccessfully to modify the macro in this thread, which is very similar to what I'm dealing with:

http://www.excelforum.com/showthread...ell+split+rows

You see, I have exact same type of table as in the thread above with the exception that data in the cells is separated by semicolons. I'd like to split cell data in column E to new rows and then copy data from other cells (unchanged) in colums A through D to the new rows. I'm sure this is something rather simple for an experienced VBA user, but well beyond me.

Could anyone please help? I have attached an excel sheet as a sample of the problem. Thanks!

[Richard Buttrey]

Hi,

It's not at all clear (at least to me) what it is you're trying to achieve. In any case this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), containing real words and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

For instance but not exclusively what rule determines that Company B has the number of word split as 1, 1 & 2, 1 & 2 & 3, 4, 5, 6 & 7 & 8, 9

What rule determines the spacing of the Company A words

Incidentally, would you change your location to something sensible. It often helps if we need to consider your time zone, regional settings, list separators, currency...etc.

[newdoverman]

I modified the char(10) to char(59) and added a title row to your sample data. The title row starts in A1 and the data actually starts in A2. Right click the page tab, click on View Code and copy this code and paste into the module presented. On the Developer tab click on Macros and choose "SplitltEnter2" and click on Run.

Please Login or Register  to view this content.

This code is taken directly from the link that you provided with char(10) changed to char(59).

Here is your worksheet after the macro has been run. The macro should be included (I hope).

[mike7952]

Here's another way

Please Login or Register  to view this content.

[ex2012]

Guys, you absolutely rock. Your macros worked like a charm even though you worked on a non-representative data.

Richard, I admit that real data is always best, but it's just impossible. I'm sure you understand.
PS. Sorry for not responding earlier. I get the message that "Firefox has detected that the server is redirecting the request for this address in a way that will never complete", and "Sucuri CloudProxy - Backend Server timeout", and "The database has encountered a problem.". This under firefox and chrome. I haven't tried other browsers.

This has been solved! Thank y'all!