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

Answer 3

ALT+ENTER creates a character called vbLF (for visual basic Line Feed). You simply want to replace the vbLF with a space, like so:

Sub test()
    Dim str As String
    str = Range("A1")
    Debug.Print str
    str = Replace(str, vbLf, " ")
    Debug.Print str
End Sub

To place it in a loop:

Sub test()
    dim i as integer
    Dim str As String

    for i = 1 to 10 '(however many you want, can also be dynamic - use XlUp if you want to)
       str = Range("A" & i)
       Debug.Print str
       str = Replace(str, vbLf, " ")
       Debug.Print str
    next
End Sub

Answer 4

A little search on your favorite browser: https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/

if it's a "one shot" no need to create a VBA macro. Replace carriage returns by a space, and the job should be done

Answer 5

I think this will do what you want:

Sub Macro1()

    Cells.Replace What:=vbLf, Replacement:=" ", LookAt:=xlPart

End Sub