Remove line breaks

M

Maia

Hi,
I have received a file that has a lot of rows in one cell. These are made by
using line break (ALT+ENTER). How can I get these rows so that each one is
in their own cell, one below the other.

Thanks!
Maya
 
E

ElsiePOA

One way -

First, use "Data" - "Text to Columns". For the delimiter, selec
"other" and enter Alt 010. (Hold down the Alt key while entering 010)
This will seperate the lines, but put them into columns, not rows.

Second, use the =Transpose function to convert them to rows

:cool
 
M

Maia

Hi,

sorry, but when I type this Alt+010 nothing happens and therefore it will
only delimit by spaces. Should something appear in the small box when I type
this?

Maya
 
M

Max

Perhaps an alternative to try out* ..
(especially if you have a lot of rows, more than the 255 cols limit .. )
*uses Word

Assume you have

In A1:
Text1
Text2
Text3

In A2:
Text4
Text5
Text6

Select A1:A2 > Copy

In Word
----------
In a new document,
Click Edit > Paste special > Unformatted Text

Press CTRL+A
(this selects all the text)

Click Edit > Replace

Put in the box for
Find what: "
(Type a single double quote)

Leave "Replace with:" empty

Click Replace All
(Dismiss the remaining dialogs)

Select all the text > Copy

Back to Excel
-----------------
In a new sheet,
Right-click on A1 > Paste

The 6 text will be pasted into A1:A6
(6 separate rows as required)
 
E

ElsiePOA

Be sure to check the box in front of "Other". Be sure to enter Alt 01
using the Number Keypad, not the numbers on the top of the keyboard.
When you enter Alt 010, nothing will show in the box but the previe
box should show where the seperations will be.
:cool
 
Top