Hard breaks in text to soft breaks in Excel

[tbailey]

Hi,

Am importing data into Excel from raw text files. Text files contain
'hard' line breaks, but I need all data to be in a single cell with
'soft' line breaks... ie alt+Enter...

Is there any easy way to do this?

Please help?!!!!!

 

[Guest]

Use Edit, Replace in Word to replace all hard returns with $. The following
macro will change only the $ in column A to manual line breaks. I will work
on the macro to expand it:
Sub maakparagraaf()
For i = 1 To 20
kontrole = 0
While kontrole = 0
kontrole = 1
diewoord = Cells(i, 1).Value
lengte = Len(diewoord)
tel = 1
dollarcount = 0
While Mid(diewoord, tel, 1) <> "$" And tel <= lengte
tel = tel + 1
If Mid(diewoord, tel, 1) = "$" Then
dollarcount = dollarcount + 1
kontrole = 0
End If
Wend
If dollarcount > 0 Then
Cells(i, 1) = Mid(diewoord, 1, tel - 1) & Chr(10) &
Mid(diewoord, tel + 1)
End If
Wend
Next i
End Sub

 

[Dave Peterson]

Saved from a previous post:

If your cells in your word table contain paragraph mark or linebreak characters,
then excel will bring them over as separate cells.

One way around it is to convert those paragraph marks & linebreaks to unique
characters, then copy|paste and then convert them back to linefeeds.

I like this technique (inside a copy of the word file):
Select your table.
Edit|replace|Special (show More if required)
Find what: (paragraph mark under Special button)
replace with: $$$$$ (if $$$$$ doesn't appear in the table)
replace all

Same thing with Manual Line break (from under Special).

Now copy the table into Excel.

Edit|Replace
Replace what: $$$$$
Replace with: hit and hold the alt and type 0010 from the number pad--not above
QWERTY.

It may look like you haven't done anything, but if you did it right, you
replaced $$$$$ with alt-enter.
Replace all.

Don't forget to close the word document without saving (or hit undo as many
times as necessary).

 

[tbailey]

Thanks guys for your help. Discovered the simplest of all solutions...
simply to select the cell and open for editing (click [F2]). Pasting t
the cell while in 'edit' mode will retain the 'line break' formatting
but will force it all into the same cell.

Easy!

Thanks again

 

[Guest]

Great method.

Saved from a previous post:

If your cells in your word table contain paragraph mark or linebreak characters,
then excel will bring them over as separate cells.

One way around it is to convert those paragraph marks & linebreaks to unique
characters, then copy|paste and then convert them back to linefeeds.

I like this technique (inside a copy of the word file):
Select your table.
Edit|replace|Special (show More if required)
Find what: (paragraph mark under Special button)
replace with: $$$$$ (if $$$$$ doesn't appear in the table)
replace all

Same thing with Manual Line break (from under Special).

Now copy the table into Excel.

Edit|Replace
Replace what: $$$$$
Replace with: hit and hold the alt and type 0010 from the number pad--not above
QWERTY.

It may look like you haven't done anything, but if you did it right, you
replaced $$$$$ with alt-enter.
Replace all.

Don't forget to close the word document without saving (or hit undo as many
times as necessary).

 

[Guest]

Hello all,

One thing I didn't catch in this thread: Can one Find & Replace the
alt+Enter character in Excel? And what is the character for alt+Enter, using
the "^" formatting?

Thanks in advance for your assistance.

Alcide

 

[Dave Peterson]

Some of those funny characters will work after you find the ASCII representation
for that character--but some won't.

In this case, alt-enter has a code of 10.

Find two empty cells.
Type alt-enter (when you're on the formulabar) for the first cell.
type =code(a1) (change A1 to that other cell's address).

You'll see 10.

So you can select your range and do
edit|replace
what: hit and hold the alt-key while typing 0010 on the numeric keypad
(not the number keys above QWERTY)

with: (whatever you want)
and continue.

In this case (alt-0010 or alt-enter), you could also use ctrl-j.

But some characters (Carriage control for instance) has a code of 13.
alt-0013 won't work in the edit|Find dialog.

Chip Pearson has a very nice addin that can show you those codes:
http://www.cpearson.com/excel/CellView.htm

Hello all,

One thing I didn't catch in this thread: Can one Find & Replace the
alt+Enter character in Excel? And what is the character for alt+Enter, using
the "^" formatting?

Thanks in advance for your assistance.

Alcide

Thanks guys for your help. Discovered the simplest of all solutions....
simply to select the cell and open for editing (click [F2]). Pasting to
the cell while in 'edit' mode will retain the 'line break' formatting,
but will force it all into the same cell.

Easy!

Thanks again.