Text to rows

buckeyebrian

New Member
Joined
Sep 13, 2006
Messages
3
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,078
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 24, 2003
Messages
123
Use Data->Text To Columns and specify the delimiter as 'Alt + 010' on your keypad.

This will split out the text.
 

buckeyebrian

New Member
Joined
Sep 13, 2006
Messages
3
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,078
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 13, 2006
Messages
3
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!!
 

Forum statistics

Threads
1,141,628
Messages
5,707,503
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top