Removing All Spaces in String

Question

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 TRUCKING@GMAIL.COM
ALEZZZZ@AOL. COM.

These just contain regular whitespace, but are skipped over.

[`w = WorksheetFunction.Clean(w)`](https://msdn.microsoft.com/en-us/library/office/ff837762.aspx) — Slai, Feb 17 '17 at 19:58
Can you provide a string that it doesn't work for? That will be helpful in reproducing the issue. — Kyle, Feb 17 '17 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. — Ryan Wildry, Feb 17 '17 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 TRUCKING@GMAIL.COM and ALEZZZZ@AOL. COM. As you can see, these just contain regular whitespace, but are skipped over for some reason. — PatrykChristopher, Feb 17 '17 at 20:44
Where is the code that "selects and goes through all the cells"? If the posted code is the code you're having issues with, it's not doing that. Because looping over `Selection.Cells` only loops over the cells that are, well, selected. Put `MsgBox Selection.Cells.Address` right before your loop and it will tell you exactly what it's going to loop over. — Comintern, Feb 17 '17 at 22:01
Possible duplicate of [VBA unable to remove the spaces](https://stackoverflow.com/questions/7400293/vba-unable-to-remove-the-spaces) — tripleee, Mar 26 '19 at 08:11
I don't see how this is a duplicate of the proposed dup-target. The dup-target is explicitly about the `trim()` function, which this question is not asking bout. In no place in the proposed target does it describe how to remove all spaces. Even Excel's version of `TRIM()` reduces the number of spaces down to one between words, but doesn't remove all of them (i.e. what this question is asking about). — Makyen, Mar 26 '19 at 23:54

score 8 · Answer 1 · answered Feb 17 '17 at 20:08

8

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

answered Feb 17 '17 at 20:08

Comintern

21,855
5
33
80

I have tried your solution, and have gotten the same result. There are still spaces in a few of the emails. – PatrykChristopher Feb 17 '17 at 20:38
@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`? – Comintern Feb 17 '17 at 20:49
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 Feb 17 '17 at 20:54
@PatrykChristopher - That means your loop over `Selection` isn't hitting those cells. You don't happen to have error handling turned off do you? – Comintern Feb 17 '17 at 21:07
That's rather strange, considering there are over 300,000 cells it works on, and only about 80 of those cells are the ones that don't have the whitespaces removed. As far as error handling, I'm not sure how to do that. I apologize, I'm still brand new to VBA and learning. – PatrykChristopher Feb 17 '17 at 21:40
@PatrykChristopher - Tools->Options->General->Error Trapping. Select `Break on All Errors`. If you don't hit one, change it to `Break on Unhandled Errors`. – Comintern Feb 17 '17 at 21:43
It was on 'Break on Unhandled Errors' the whole time, and I tested it with 'Break on All Errors' and I didn't receive any errors. Same result, whitespaces are there. – PatrykChristopher Feb 17 '17 at 21:55
1

@user1274820 - You should probably direct that to the OP - I already know all of this, but I'm the one that you're pinging. – Comintern Feb 17 '17 at 22:11
@PatrykChristopher see above – user1274820 Feb 17 '17 at 23:06
I solved the issue, thank you for your generous contributions! – PatrykChristopher Feb 20 '17 at 14:45

score 5 · Answer 2 · answered Feb 17 '17 at 22:11

5

Try this:

Sub NoSpaces()
Selection.Replace " ", ""
End Sub

answered Feb 17 '17 at 22:11

user1274820

7,786
3
37
74

1

This worked perfectly! I guess it was a problem with the selection of the cells. Thank you very much! – PatrykChristopher Feb 20 '17 at 14:43

score 2 · Answer 3 · answered Feb 17 '17 at 20:11

2

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

answered Feb 17 '17 at 20:11

Gi1ber7

632
1
11
22

score 0 · Answer 4 · answered Feb 17 '17 at 21:07

0

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

Cells.Replace " ", ""

answered Feb 17 '17 at 21:07

Slai

22,144
5
45
53

score 0 · Answer 5 · answered Feb 18 '17 at 01:27

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.

answered Feb 18 '17 at 01:27

Waseem

19
3

Yes, I have a Trim macro that takes care of the leading and trailing spaces, thank you for your input! – PatrykChristopher Feb 20 '17 at 14:44

JayLlanz · Answer 6 · 2019-03-26T08:05:27.890

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

score 0 · Answer 7 · answered Aug 13 '20 at 01:04

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

Removing All Spaces in String

7 Answers7

Linked