Replacing spaces with a line end

[Fat Doris]

How can I replace two spaces with a line end, throughout one column in a
spreadsheet. The context is that in one column the entire address has been
entered in one cell without line breaks, and I need to use that column in a
mailmerge so want the address to print as normal on different lines.

Thank you

 

[Warren Easton]

Hi Doris,

The only way I can think of is to
Select the cell, go to the Data tab and click on text to Columns and follow
the instructions, then select all the cells, copy-paste special -click the
transpose box then click OK --


Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.

 

[Fat Doris]

Thanks but I'm afraid that doesn't do the trick - everything's all over the
place. I know there is a way to search for two spaces and replace with a
line break because I'd used it before, and I've filed it somewhere as useful
info, but can I find it when I want it!!!!!

 

[Dave Peterson]

Select the range to fix (at least 2 cells)
Edit|replace
what: (two space characters)
with: ctrl-j
replace all

Ctrl-j is the same as the alt-enter. You may need to change the formatting to
wraptext, too.

 

[Fat Doris]

That's it - thanks a lot.

Select the range to fix (at least 2 cells)
Edit|replace
what: (two space characters)
with: ctrl-j
replace all

Ctrl-j is the same as the alt-enter. You may need to change the formatting to
wraptext, too.

 

[Fat Doris]

I've just used this to split given names and surnames into two columns and it
saves so much time. Wish I'd known this years ago. Thanks.