undoing alt-enter

**bcoates** · 09-07-2007, 11:23 AM

I have a spreadsheet where one column is the customer name. The column next to it is the items the customer purchased. Normally the items column has a separate row for each item, but someone used the alt-enter method to put all the items in one cell instead of each item in its own row. Is there a way to undo this so each item is in its own row???

**Ron Coderre** · 09-07-2007, 12:18 PM

Yes....but it's a 2-step process,

Step_1: Break apart the values into separate columns.
Select the one-column range of values containing the "alt-entered' items.
(Make sure there are enough blank columns to the right of that range to accommodate the number of items in the cells)

<data><text-to-columns>
Check: Delimited...........Click [Next]
Check: Other
....select the "other box"
...Ctrl+J <--That puts an invisible carriage return in the box.
Click [Finish]

If A1 contains the below list separated by Alt-Enters
1
2
3
4

After you follow the above steps, the results will be
A1: 1
B1: 2
C1: 3
D1: 4

That was the easy part.

Step_2: Convert the horizontal arrays to vertical arrays.
Make room below each horizontal list
Copy/Paste_Special.Transpose each list

Post back if you have more questions.

**bcoates** · 09-07-2007, 12:56 PM

So i guess to do this for 3000 rows i shuold record a macro and put the output into a different sheet?

**Ron Coderre** · 09-07-2007, 01:31 PM

Originally Posted by bcoates

So i guess to do this for 3000 rows i shuold record a macro and put the output into a different sheet?

Hmmm.....maybe not....

If you only need to break out one column

Open MS WordPad: [start button].[Run]write.exe...Click [OK]

In Excel:
Copy the range of "Alt-Entered" cells

Paste them into WordPad!
You'll get one long vertical column of the values because WordPad knows what to do with a carriage return.

Then....Copy the WordPad column...and Paste it into a safe place in Excel.
Each of values will land in a vertical array of cells.

Does that help?

**bcoates** · 09-10-2007, 11:09 AM

That worked, kind of. the problem is i have another column that has to keep up with it. I have attached a xls workbook. Sheet 1 is what i have sheet 2 is what i want. This has been scaled down but if there is a macro that can undo the ctrl-j and keep the first column let me know.
BTW thanks for all of your help so far