vba Excel：将多行文本的单元格合并为一行文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31749507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Cell with multiple lines of text into one line of text
提问by Clauric
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
任何帮助深表感谢
回答by KFichter
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 allbuttton.
单击Replace all按钮。
回答by Gary's Student
Try this short macro:
试试这个简短的宏：
Sub dural()
Dim rng As Range
Set rng = Selection
rng.Replace what:=Chr(10), lookat:=xlPart, replacement:=" "
End Sub
回答by David G
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
回答by Maxime Porté
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/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
如果是“一次性”，则无需创建 VBA 宏。用一个空格替换回车，工作应该就完成了
回答by Tony Dallimore
I think this will do what you want:
我认为这会做你想做的：
Sub Macro1()
Cells.Replace What:=vbLf, Replacement:=" ", LookAt:=xlPart
End Sub
