blocks|key|4013360|text|只需使用正则表达式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4013361|'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|code-block|syntax|javascript|4013362|这样叫它：|4013363|Sub+NoSpaces()
++++Dim+w+As+Range

++++For+Each+w+In+Selection.Cells
++++++++w.Value+=+RemoveWhiteSpace(w.Value)
++++Next
End+Sub|4013364|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Just use a regular expression:

<pre><code>'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
</code></pre>

Call it like this:

<pre><code>Sub NoSpaces()
 Dim w As Range

 For Each w In Selection.Cells
 w.Value = RemoveWhiteSpace(w.Value)
 Next
End Sub
</code></pre>

blocks|key|1902884|text|使用“替换”示例...=替换(C1:C18，“"，"")|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1902885|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Use "Substitute"
Example... 
=SUBSTITUTE(C1:C18," ","")

blocks|key|4013425|text|因为您假设Selection.Cells包含工作表上的所有单元格。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4013426|Cells.Replace+"+",+""|code-block|syntax|javascript|4013427|entityMap^0|5|F|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Because you assume that <code>Selection.Cells</code> includes all cells on the sheet.

<pre><code>Cells.Replace " ", ""
</code></pre>

blocks|key|4013435|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4013436|Sub+NoSpaces()
Selection.Replace+"+",+""
End+Sub|code-block|syntax|javascript|4013437|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>Sub NoSpaces()
Selection.Replace " ", ""
End Sub
</code></pre>

blocks|key|1902961|text|为了补充来自所有优秀贡献者的优秀建议，请尝试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1902962|TRIM或LTRIM或RTRIM，您可以在此处阅读有关这些函数的更多信息：|1902963|https://msdn.microsoft.com/en-us/library/office/gg278916.aspx|offset|length|1902964|现在，这不会删除嵌入的空格(字母之间的空格)，但会删除任何前导空格和尾随空格。|1902965|希望这能有所帮助。|1902966|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|1P|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@$F|V|G|W|1|X]]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|Z|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|E]]]]

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:

<a href="https://msdn.microsoft.com/en-us/library/office/gg278916.aspx" rel="nofollow noreferrer">https://msdn.microsoft.com/en-us/library/office/gg278916.aspx</a>

Now this does not remove embedded spaces (spaces in between the letters) but it will remove any leading and trailing spaces.

Hope this helps.

blocks|key|5431843|text|空间问题与Excel+ok，我看到这两种类型的空间的唯一方式是通过转换他们的Ascii代码值，我现在做here来解释我做的这个函数，它将只是过滤字符串字符的字符检查，如果它等于我提到的两种类型的空间。如果不是，它会将该字符连接到字符串中，该字符串将是循环后的最终值。希望这能有所帮助。谢谢。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|5431844|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|code-block|syntax|javascript|5431845|entityMap|0|LINK|mutability|MUTABLE|url|http://www.unit-conversion.info/texttools/ascii/^0|0|A|1F|4|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Space Problem with Excel
ok, the only way i see this two types of space is by converting their Ascii code value of which I do it <a href="http://www.unit-conversion.info/texttools/ascii/" rel="nofollow noreferrer">here</a> 
now 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.

<pre><code>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 &amp; "" &amp; sstr
 End If

Next x
spaceremove = nstr
End Function
</code></pre>

blocks|key|4013539|text|我复制了一个包含数据的HTML表格，并将其粘贴到excel中，但是单元格中充满了不需要的空格，并且这里发布的所有方法都不起作用，所以我进行了调试，发现它实际上不是空格(ASCII32)，而是Non-breaking+space(ASCII160)或HTML+&nbsp;|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4013540|因此，为了让它与不间断空格字符一起工作，我这样做了：|4013541|Sub+NoSpaces()
++++Dim+w+As+Range

++++For+Each+w+In+Selection.Cells
++++++++w.Value+=+Replace(w.Value,+"+",+vbNullString)
++++++++w.Value+=+Replace(w.Value,+Chr(160),+vbNullString)
++++Next
End+Sub|code-block|syntax|javascript|4013542|entityMap|0|LINK|mutability|MUTABLE|url|https://en.wikipedia.org/wiki/Non-breaking_space^0|3L|6|2N|I|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|6|7|10|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

I copied a HTML table with data and pasted in excel but the cells were filled with unwanted space and all methods posted here didn't work so I debugged and I discovered that it wasn't actually space chars (ASCII 32) it was <a href="https://en.wikipedia.org/wiki/Non-breaking_space" rel="nofollow noreferrer">Non-breaking space</a>) (ASCII 160) or HTML <code>&amp;nbsp;</code>
So to make it work with that Non-breaking space char I did this:
<pre><code>Sub NoSpaces()
 Dim w As Range

 For Each w In Selection.Cells
 w.Value = Replace(w.Value, &quot; &quot;, vbNullString)
 w.Value = Replace(w.Value, Chr(160), vbNullString)
 Next
End Sub
</code></pre>

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.

My code:

<pre><code>Sub NoSpaces()
 Dim w As Range

 For Each w In Selection.Cells
 w = Replace(w, " ", "")
 Next
End Sub
</code></pre>

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.

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.

A few examples:

<pre><code>1 STAR MOVING @ ATT.NET
322 TRUCKING@GMAIL.COM
ALEZZZZ@AOL. COM. 
</code></pre>

These just contain regular whitespace, but are skipped over.

Removing All Spaces in String

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我创建了一个宏，用于删除字符串中的所有空格，特别是电子邮件地址。然而，它只删除了大约95%的空格，只留下了一小部分。我的代码：Sub NoSpaces()    Dim w As Range    For Each w In Selection.Cells        w = Replace(w, " ", "") ...

问删除字符串中的所有空格
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问删除字符串中的所有空格EN