Excel: Cell with multiple lines of text into one line of text

Question

I have about 4000 cells, each with about 4 separate lines of text. Each line of texts has been entered using the ALT+ENTER method.

Can anybody suggest a way, either VBA or Excel command/method to convert each of of these cells into one line, with spaces between the words.

Example: Current Cell 1:

• About
• Horse
• Dog

Need: Cell 1:

• About Horse Dog

Any help is much appreciated

Answer 1

To do this without VBA:

Use find and replace.

In the find what box, hold ALT and type 0010 (ASCII code for line feed is 10 - you will not see anything, but it's a new line character).

In the replace box, simply type a space character or any delimiter.

Click on Replace all buttton.

Answer 2

Try this short macro:

Sub dural()
    Dim rng As Range
    Set rng = Selection
    rng.Replace what:=Chr(10), lookat:=xlPart, replacement:=" "
End Sub