17

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.

Improve this question
7
  • w = WorksheetFunction.Clean(w) Commented Feb 17, 2017 at 19:58
  • 2
    Can you provide a string that it doesn't work for? That will be helpful in reproducing the issue. Commented Feb 17, 2017 at 20:05
  • Are the replacements of spaces not occurring where you didn't select cell before running the code? This code only applies to cells you've selected. Commented Feb 17, 2017 at 20:19
  • 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. As for providing strings, I will give a few general examples: 1 STAR MOVING @ ATT.NET, 322 [email protected] and ALEZZZZ@AOL. COM. As you can see, these just contain regular whitespace, but are skipped over for some reason. Commented Feb 17, 2017 at 20:44
  • 3
    Possible duplicate of VBA unable to remove the spaces Commented Mar 26, 2019 at 8:11

7 Answers 7

Reset to default
10

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
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I have tried your solution, and have gotten the same result. There are still spaces in a few of the emails.
@PatrykChristopher - Does it replace them if you explicitly refer to the cell that they're in? I.e. Range("A1").Value = RemoveWhiteSpace(Range("A1").Value)? Where are you setting Selection?
I went ahead and ran the function separately =RemoveWhiteSpace(CellNumber), and it worked perfectly. However when running the macro, it doesn't remove the whitespace for some reason.
@PatrykChristopher - That means your loop over Selection isn't hitting those cells. You don't happen to have error handling turned off do you?
@user1274820 - You should probably direct that to the OP - I already know all of this, but I'm the one that you're pinging.
|
6

Try this:

Sub NoSpaces()
Selection.Replace " ", ""
End Sub
Improve this answer

1 Comment

This worked perfectly! I guess it was a problem with the selection of the cells. Thank you very much!
3

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

Improve this answer

Comments

1

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 Non-breaking space) (ASCII 160) or HTML  

So to make it work with that Non-breaking space char I did this:

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
Improve this answer

Comments

0

Because you assume that Selection.Cells includes all cells on the sheet.

Cells.Replace " ", ""
Improve this answer

Comments

0

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:

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.

Improve this answer

1 Comment

Yes, I have a Trim macro that takes care of the leading and trailing spaces, thank you for your input!
0

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

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.