Text to rows

[buckeyebrian]

I am trying to develop a macro that takes cells that have multiples lines in them and converts them to individual rows. The spreadsheet I am working on had single cells that look like this

Lehr Timing
Coating Challenge
20 min - color change

All of that information is in one cell. What I need to be able to do is split that cell into three cells so that each cell contains one line of the orignal cell. Does anyone have any idea of how to do this? The data that is put into the cell is not consistent so I don't believe there is a way to use the transpose and text to column method. Any help with this would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

There is a "soft" carriage return in the cells that separates the data into three rows within the same cell. I believe that there may be a way to use Text to Columns using the "soft" carriage return as the Delimiter.

Try searching the board for this. I think I have seen it done on this board in the past.

 

[Sean Stevens]

Use Data->Text To Columns and specify the delimiter as 'Alt + 010' on your keypad.

This will split out the text.

 

[buckeyebrian]

I have excel 2000. Is it possible to use the method that Sean Stevens mentioned above? When I go to enter the delimiter it only allows me to enter in one character. I am wonderinf if your solution will only work for Excel 2003. Anyone have any thoughts on this?

 

[Joe4]

Seans method does work in 2000 (I have 2000 and have confirmed). You are probably just misunderstanding the steps he is giving you.

If you check the "Other" delimiter, in the box next to Other where you enter the delimiter, hold down the ALT key while you type in 010. This will only enter one character (though it will appear invisible), but should break up your data into three columns.

Follow it through to the end and check out the results.

 

[buckeyebrian]

I accidentaly was not using the number pad when entering the numbers so it wasn't working for me... I will just attribute it to the fact that I just ate lunch. Other than that it seems to work perfect. What exactly is the ALT + 010? Is it just a "soft carriage return" that you mentioned before? Thanks for the help!!

 

[Joe4]

It is the "soft carriage return" or "line feed".

See here:
http://www.cisco.com/univercd/cc/td/doc/product/software/ios123/123cgcr/fun_r/cfrappen.htm