Geek Answers Handbook

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

I have about 4,000 cells, each of which contains about 4 separate lines of text. Each line of texts is entered using the ALT + ENTER method.

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

Example: Current Cell 1:

  • ABOUT
  • Horse
  • Dog

Need: Cell 1:

  • About a horse dog

Any help is much appreciated

+5
source share
5 answers

Try this short macro:

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

ALT + ENTER creates a character called vbLF (for visual base channel). You just want to replace vbLF with a space, for example:

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

Put 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
+6
source

For this, without VBA:

Use search and replace.

In the search window, holding ALT, enter 0010 (the ASCII code for the line feed is 10 - you will not see anything, but this is a new line character).

In the replacement field, simply enter a space character or any separator.

Click the Replace all buttton button.

+6
source

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

if "one shot" does not need to create a VBA macro. Replace the carriage return with a space, and the task must be completed.

+3
source

I think this will do what you want:

 Sub Macro1() Cells.Replace What:=vbLf, Replacement:=" ", LookAt:=xlPart End Sub
+3
source

More articles:


All Articles