vba 删除字符串中的所有空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42306358/
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 All Spaces in String
提问by PatrykChristopher
I created a macro for removing all whitespace in a string, specifically an email address. However it only removes about 95% of the whitespace, and leaves a few.
我创建了一个宏来删除字符串中的所有空格，特别是电子邮件地址。然而，它只删除了大约 95% 的空白，并留下了一些。
My code:
我的代码：
Sub NoSpaces()
Dim w As Range
For Each w In Selection.Cells
w = Replace(w, " ", "")
Next
End Sub
Things I have tried to solve the issue include:
我试图解决这个问题的事情包括：
~ Confirmed the spaces are indeed spaces with the Code function, it is character 32 (space)
~ Used a substitute macro in conjuction with the replace macro
~ Have additional macro utilizing Trim function to remove leading and trailing whitespace
~ Made a separate macro to test for non-breaking spaces (character 160)
~ Used the Find and Replace feature to search and replace spaces with nothing. Confirmed working.
~ 用 Code 函数确认空格确实是空格，是字符 32（空格）
~ 使用替换宏与替换宏结合使用
~ 有额外的宏使用 Trim 函数去除前导和尾随空格
~ 制作了一个单独的宏来测试对于不间断空格（字符 160）
~使用查找和替换功能来搜索和替换空格。确认工作。
I only have one cell selected when I run the macro. It selects and goes through all the cells because of the Selection.Cells part of the code.
当我运行宏时，我只选择了一个单元格。由于代码的 Selection.Cells 部分，它选择并遍历所有单元格。
A few examples:
几个例子：
1 STAR MOVING @ ATT.NET
322 [email protected]
ALEZZZZ@AOL. COM.
These just contain regular whitespace, but are skipped over.
这些只包含常规空格，但被跳过。
回答by Comintern
Just use a regular expression:
只需使用正则表达式：
'Add a reference to Microsoft VBScript Regular Expressions 5.5
Public Function RemoveWhiteSpace(target As String) As String
With New RegExp
.Pattern = "\s"
.MultiLine = True
.Global = True
RemoveWhiteSpace = .Replace(target, vbNullString)
End With
End Function
Call it like this:
像这样调用它：
Sub NoSpaces()
Dim w As Range
For Each w In Selection.Cells
w.Value = RemoveWhiteSpace(w.Value)
Next
End Sub
回答by user1274820
Try this:
尝试这个：
Sub NoSpaces()
Selection.Replace " ", ""
End Sub
回答by Gi1ber7
Use "Substitute" Example... =SUBSTITUTE(C1:C18," ","")
使用“替换”示例... =SUBSTITUTE(C1:C18," ","")
回答by Slai
Because you assume that Selection.Cellsincludes all cells on the sheet.
因为您假设Selection.Cells包括工作表上的所有单元格。
Cells.Replace " ", ""
回答by Waseem
And to add to the excellent advice from all the great contributors, try the
为了增加所有伟大贡献者的出色建议，请尝试
TRIM or LTRIM, or RTRIM and you can read more about these functions here:
TRIM 或 LTRIM 或 RTRIM，您可以在此处阅读有关这些函数的更多信息：
https://msdn.microsoft.com/en-us/library/office/gg278916.aspx
https://msdn.microsoft.com/en-us/library/office/gg278916.aspx
Now this does not remove embedded spaces (spaces in between the letters) but it will remove any leading and trailing spaces.
现在这不会删除嵌入的空格（字母之间的空格），但会删除任何前导和尾随空格。
Hope this helps.
希望这可以帮助。
回答by JayLlanz
Space Problem with Excelok, the only way i see this two types of space is by converting their Ascii code value of which I do it herenow to explain this function i made, it will just filter the string character by character checking if its equal to the two types of space i mentioned. if not it will concatenate that character into the string which will be the final value after the loop. hope this helps. Thanks.
Excel 的空格问题好的，我看到这两种类型的空格的唯一方法是转换它们的 Ascii 代码值，我现在在这里做它 来解释我制作的这个函数，它只会逐个字符地过滤字符串，检查它是否相等我提到的两种类型的空间。如果不是，它将将该字符连接到字符串中，该字符串将成为循环后的最终值。希望这可以帮助。谢谢。
Function spaceremove(strs) As String
Dim str As String
Dim nstr As String
Dim sstr As String
Dim x As Integer
str = strs
For x = 1 To VBA.Len(str)
sstr = Left(Mid(str, x), 1)
If sstr = " " Or sstr = "?" Then
Else
nstr = nstr & "" & sstr
End If
Next x
spaceremove = nstr
End Function
