vba 如何删除隐藏的引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43911731/
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
How to remove hidden quotes
提问by Vadiraj Katti
I've Excel sheet that has few text columns. These text columns are Email Messages. The data from this sheet will be used to send mails.
我有几个文本列的 Excel 工作表。这些文本列是电子邮件消息。此表中的数据将用于发送邮件。
There data looks fine in Excel but when the message is copied to the Email body quotes are appearing in the beginning and end of the message.
Excel 中的数据看起来不错，但是当消息被复制到电子邮件正文时，引号出现在消息的开头和结尾。
I researched online and found out that these are unwanted characters. I tried removing the "using following formula.
我在网上研究，发现这些是不需要的字符。我尝试"使用以下公式删除。
=SUBSTITUTE(SUBSTITUTE(CLEAN(K1),CHAR(127),""),CHAR(160),"")
However the problem is that there are multiple columns with this problem so this method is not very feasible option for me. Also another problem is that after this the cell loses the formatting.
然而问题是有多个列有这个问题，所以这个方法对我来说不是很可行的选择。还有一个问题是，在此之后单元格会丢失格式。
Please help me resolve this, I'm looking for a Find and Replace method if possible. Worst case scenario would be a macro.
请帮我解决这个问题，如果可能的话，我正在寻找一种查找和替换方法。最坏的情况是宏。
Thanks in advance.
提前致谢。
回答by Nathan_Sav
Cells.Replace What:=Chr(127), Replacement:=vbNullString
Cells.Replace What:=Chr(160), Replacement:=vbNullString
回答by Gowtham Shiva
Your cells in your excel sheet contains multiple lines of data within a data, which means all lines in the cell are entered with carriage return. (Enter Key)
Excel 工作表中的单元格在一个数据中包含多行数据，这意味着单元格中的所有行都以carriage return. （回车键）
If you copy and paste such cells to a txt file, you will get the text within a " ". The " "are not actually quotes, but text with carriage return.
如果您将此类单元格复制并粘贴到 txt 文件中，您将获得 .txt 文件中的文本" "。在" "实际上并没有报价，但回车文本。
Just use the formula and let me know if it works,
只需使用公式并告诉我它是否有效，
=SUBSTITUTE(A1,CHAR(10)," ")
=SUBSTITUTE(A1,CHAR(10)," ")
