vba 将带有换行符的txt文件导入Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8759231/
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
Import txt file with line breaks into Excel
提问by T3rm1
while working on an export to Excel I discovered the following problem.
在导出到 Excel 时，我发现了以下问题。
If you create a table where one cell has a line break and you save the document as a txt file it will look like this:
如果您创建一个表格，其中一个单元格有换行符，并将文档另存为 txt 文件，它将如下所示：
"firstLine<LF>secondLine"<TAB>"secondColoumn"
When I open this file in Excel the line break is gone and the first row has only one cell with the value firstLine
当我在 Excel 中打开这个文件时，换行符消失了，第一行只有一个值为firstLine 的单元格
Do you know if it is somehow possible to keep the line breaks?
你知道是否有可能保持换行符？
EDIT: Applies to Excel2010. Don't know if other versions behave different.
编辑：适用于 Excel2010。不知道其他版本是否表现不同。
EDIT2: Steps to reproduce:
EDIT2：重现步骤：
- Open blank excel sheet
- Enter text (first column with line break, second colum not important)
- Save as Unicode Text (txt) // all other txt don't work as well
- Close Excel file
- File->Open
- No changes in the upcoming dialog.
- 打开空白的excel表
- 输入文本（第一列带换行符，第二列不重要）
- 另存为 Unicode 文本 (txt) // 所有其他 txt 都不起作用
- 关闭 Excel 文件
- 文件->打开
- 在即将到来的对话中没有变化。
The excel file has now 2 rows which is wrong.
excel 文件现在有 2 行，这是错误的。
采纳答案by T3rm1
I was finally able to solve the problem! yay :D
我终于能够解决问题了！是的：D
CSV:
CSV：
The german Excel needs a semicolon as a separator. Comma doesn't work.
德国Excel需要分号作为分隔符。逗号不起作用。
Note:This is only true when the file is encoded as UTF-8 with BOM at the beginning of the file. If it's ASCII encoded comma does work as a delimiter.
注意：这仅在文件被编码为文件开头带有 BOM 的 UTF-8 编码时才适用。如果是 ASCII 编码的逗号确实可以作为分隔符。
TXT:
文本：
The encoding has to be UTF-16LE. Also it needs to be tab delimited.
编码必须是 UTF-16LE。它也需要用制表符分隔。
Important:The files will still be displayed incorrect if you open them with the "File->Open" dialog and "import" them. Draging them into Excel or opening with double click works.
重要提示：如果您使用“文件->打开”对话框打开文件并“导入”它们，文件仍将显示不正确。将它们拖入 Excel 或双击打开即可。
回答by brettdj
It isn't a problem - in the sense of expected behaviour - this is inherent when you save text as Unicodeor as Text (tab delimited)
这不是问题 - 在预期行为的意义上 - 当您将文本另存为Unicode或另存为时，这是固有的Text (tab delimited)
If you save the file as unicode and then either
如果您将文件另存为 unicode，然后
- Open it in Notepad
- Import it in Excel
- 用记事本打开
- 在 Excel 中导入
you will see that the cells with linebreaks are surrounded by ""
你会看到带有换行符的单元格被“”包围
The example below shows two linebreaks
下面的例子显示了两个换行符
A1has an entry separated using Alt+EnterB1has an enry using the formulaCHAR(10)
A1有一个使用 Alt+Enter 分隔的条目B1有一个使用公式的条目CHAR(10)
The picture also shows what notepad sees on a saved Unicodeversion
图片还显示了记事本在保存的Unicode版本上看到的内容
Suggested Workaround 1- Manual Method
建议的解决方法 1 - 手动方法
- In Excel, choose Edit>Replace
- Click in the Find What box
- Hold the Alt key, and (on the number keypad), type 0010
Replace with a double pipe delimiter
- Save as Unicode
- Then reverse the process when needed to reinsert the linebreaks
- 在 Excel 中，选择“编辑”>“替换”
- 单击“查找内容”框
- 按住 Alt 键，然后（在数字键盘上）键入 0010
替换为双管道分隔符
- 另存为 Unicode
- 然后在需要重新插入换行符时反转该过程
This can be done easily in VBA
这可以在 VBA 中轻松完成
Suggested Workaround 2 - VBA alternative
建议的解决方法 2 - VBA 替代方案
Const strDelim = "||"
Sub LBtoPIPE()
ActiveSheet.UsedRange.Replace Chr(10), strDelim, xlPart
ActiveSheet.UsedRange.Replace "CHAR(10)", strDelim, xlPart
End Sub
Sub PIPEtoLB()
ActiveSheet.UsedRange.Replace strDelim, Chr(10), xlPart
ActiveSheet.UsedRange.Replace strDelim, "CHAR(10)", xlPart
End Sub
