How do I separate multiple values in one cell in to multiple new cells?

I have encountered a problem while working with large lists in Excel. I have written multiple values in one cell, separated by line break, but now i need to separate these values into separate cells and i have no idea how to do this in a relatively easy way. My only solution right now is to just write everything by hand again but it is a really big spreadsheet so I would be very thankful if someone knew a function that could take me around this problem. To further illustrate my problem here is an attachment:


workbook:

line break problem.xlsx

Please post a sample workbook rather than a pdf file (or a picture).

Gut reaction is that it might require VBA. Do you want a macro/VBA solution?

Regards, TMS

The new line character in your data is CHAR(10). So, you can use a formula to change that to something else: C3:

Formula:

Please Login or Register  to view this content.

If you then Copy and Paste Special | Values into the next column, you can then use Text to Columns with a semi-colon separator to copy them to individual cells. Once they are in separate cells, you can use Copy and Paste Special | Transpose to get them into columnar format.

Bit tedious, but it works. If you want to do more than a couple of cells at a time, it would make sense to automate it with VBA.

Regards, TMS

And, if you want the code:

Please Login or Register  to view this content.

To use it, press Alt-F11 to open the VB Editor. Insert a new module and copy and paste the code into the window. Press F5 to run the code.

Regards, TMS

Thanks for the rep.

Glad I could help you move up the evolutionary scale