删除字符串中的所有空格 [英] Removing All Spaces in String

问题描述

我创建了一个宏，用于删除字符串中的所有空格，特别是电子邮件地址.但是，它仅删除了大约95％的空白，并留下了一些空白.

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.

我的代码:

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:

〜使用Code函数确认空格确实是空格，它是字符32(空格)
〜将替换宏与替换宏结合使用
〜具有附加的利用Trim功能删除宏的前导和尾随空格的宏
〜制作了一个单独的宏以测试不间断空格(字符160)
〜使用查找和替换"功能搜索和替换空白.确认工作.

~ 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.

运行宏时，我只选择了一个单元格.由于代码的Selection.Cells部分，它选择并遍历所有单元格.

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.

一些例子:

1 STAR MOVING @ ATT.NET
322 [e-mail]
ALEZZZZ@AOL. COM. 

这些仅包含常规空格，但被跳过.

These just contain regular whitespace, but are skipped over.

推荐答案

只需使用正则表达式:

'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

这样称呼:

Sub NoSpaces()
    Dim w As Range

    For Each w In Selection.Cells
        w.Value = RemoveWhiteSpace(w.Value)
    Next
End Sub

这篇关于删除字符串中的所有空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！