vba 从单元格中删除空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44561960/
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
Removing spaces from cell
提问by Alluton
I am trying to remove all spaces from cells in a specific column. I found this question,which has an answer:How to remove spaces from an entire Excel column using VBA?
我正在尝试从特定列中的单元格中删除所有空格。我发现了这个问题，它有一个答案：如何使用 VBA 从整个 Excel 列中删除空格？
This is how I adapted the answer:
这就是我调整答案的方式：
For i = 2 To n
Cells(i, 17).Value = Replace(Cells(i, 17).Value, " ", "")
Next i
It does eliminate the space but it also eliminates a comma. For example "424, 426" changes to "424426".
它确实消除了空格，但也消除了逗号。例如，“424, 426”更改为“424426”。
I did run my code one part at a time to make sure that the problem indeed happens in that specific cope block.
我确实一次运行了一部分代码，以确保问题确实发生在特定的应对块中。
How should I change the code to make sure that only the spaces get eliminated?
我应该如何更改代码以确保只消除空格？
回答by Siddharth Rout
I think you are trying to remove the leading and trailing space. For that you do not need to loop. Here is a one liner example
我认为您正在尝试删除前导和尾随空格。为此，您不需要循环。这是一个单班轮的例子
Sub Sample()
[A1:A20] = [INDEX(TRIM(A1:A20),)]
End Sub
For explanation see Thispost
有关解释，请参阅此帖子
Edit
编辑
To remove all spaces, change your code to
要删除所有空格，请将您的代码更改为
For i = 2 To n
If InStr(1, Cells(i, 17).Value, ",") Then
Cells(i, 17).Value = Replace("'" & Cells(i, 17).Value, " ", "")
Else
Cells(i, 17).Value = Replace(Cells(i, 17).Value, " ", "")
End If
Next i
or change the formatting of the entire column to Textbefore you do the replace.
或Text在执行替换之前将整个列的格式更改为。
