Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

vba - Removing All Spaces in String

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:

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.

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:

1 STAR MOVING @ ATT.NET
322 [email protected]
ALEZZZZ@AOL. COM. 

These just contain regular whitespace, but are skipped over.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...