Excel:将多行文本转换为一行文本的单元格

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.

我有大约4000个单元格，每个单元格有大约4行不同的文本。使用ALT+ENTER方法输入每一行文本。

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.

谁能提出一种方法，VBA或Excel命令/方法，将这些单元格中的每一个转换为一行，并在单词之间留出空格。

Example: Current Cell 1:

例如:当前单元格1:

About
关于
Horse
马
Dog
狗

Need: Cell 1:

细胞需要:1:

About Horse Dog
关于马的狗

Any help is much appreciated

非常感谢您的帮助

5 个解决方案

#1

Try this short macro:

试试这个简短的宏:

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

#2

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

ALT+ENTER创建一个名为vbLF的字符(用于visual basic Line Feed)。你只需要用一个空格替换vbLF，如下所示:

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

#3

To do this without VBA:

在没有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).

在“查找什么”框中，按住ALT键并输入0010(换行符的ASCII码是10——您将看不到任何东西，但它是一个新的换行符)。

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

在“替换”框中，只需键入空格字符或任何分隔符。

Click on Replace all buttton.

单击“替换所有按钮”。

#4

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

在你最喜欢的浏览器上搜索一下:https://www.ablebits.com/office- addins-blog/2013/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/03/12/0

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

如果是“一枪”，则不需要创建VBA宏。用空格替换回车，应该完成这项工作

#5

I think this will do what you want:

我认为这将实现你想要的:

Sub Macro1()

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

End Sub

#1