vba 删除 Excel 单元格中的换行符、回车符和所有前导空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16246078/
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
Remove line breaks, return carriages, and all leading space in Excel Cell
提问by Parseltongue
I have no idea what is happening, but I have cells that contain what appears to be a return carriage. I have tried TRIM(), CLEAN(), =SUBSTITUTE(A1,CHAR(10),"")and a number of macros to remove these characters.
我不知道发生了什么，但我的单元格包含似乎是回车的内容。我已经尝试过TRIM(), CLEAN(),=SUBSTITUTE(A1,CHAR(10),"")和一些宏来删除这些字符。
The only way to remove these characters it to get the cell active, click delete near the last character, and click enter.
删除这些字符的唯一方法是激活单元格，单击最后一个字符附近的删除，然后单击 Enter。
Is there something I'm missing? Is there a way to programatically do this?
有什么我想念的吗？有没有办法以编程方式做到这一点？
回答by glh
The following macro will remove all non-printable characters and beginning and ending spaces utilising the Trim()and Clean()functions:
以下宏将使用Trim()和Clean()函数删除所有不可打印的字符以及开头和结尾的空格：
Sub Clean_and_Trim_Cells()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim s As String
For Each c In ActiveSheet.UsedRange
s = c.Value
If Trim(Application.Clean(s)) <> s Then
s = Trim(Application.Clean(s))
c.Value = s
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
回答by Angela
An easier solution is find replace: for find press alt and the numbers 010 at the same time (on the 10 keypad) and then replace with a space.
一个更简单的解决方案是查找替换：查找时同时按下 alt 和数字 010（在 10 键盘上），然后用空格替换。
You can do this as a bulk replace by just highlighting the cells that contain the carriage breaks.
您可以通过突出显示包含回车符的单元格来批量替换。
回答by Angela
I have tried
我试过了
ws.Cells(i, j) = Replace(ws.Cells(i, j), Chr(13), "")
and succeed.
并成功。
