Removing spaces from cell

Question

I am trying to remove all spaces from cells in a specific column. I found this question,which has an answer:How to remove spaces from an entire Excel column using VBA?

This is how I adapted the answer:

For i = 2 To n
    Cells(i, 17).Value = Replace(Cells(i, 17).Value, " ", "")
Next i

It does eliminate the space but it also eliminates a comma. For example "424, 426" changes to "424426".

I did run my code one part at a time to make sure that the problem indeed happens in that specific cope block.

How should I change the code to make sure that only the spaces get eliminated?

Most likely because the cell is formatted a number. Try changing the format the "General" **before** running the macro. — A.S.H, Jun 15 '17 at 08:20
@A.S.H The cells are formatted as "general. Would that be an issue? — Alluton, Jun 15 '17 at 08:39
Change the formatting of the column to `Text` and not `General` or use the method that I gave in the post below — Siddharth Rout, Jun 15 '17 at 09:01

Siddharth Rout · Accepted Answer · 2017-06-15T09:02:56.977

3

I think you are trying to remove the leading and trailing space. For that you do not need to loop. Here is a one liner example

Sub Sample()
    [A1:A20] = [INDEX(TRIM(A1:A20),)]
End Sub

For explanation see This post

Edit

To remove all spaces, change your code to

For i = 2 To n
    If InStr(1, Cells(i, 17).Value, ",") Then
        Cells(i, 17).Value = Replace("'" & Cells(i, 17).Value, " ", "")
    Else
        Cells(i, 17).Value = Replace(Cells(i, 17).Value, " ", "")
    End If
Next i

or change the formatting of the entire column to Text before you do the replace.

edited Jun 15 '17 at 09:02

answered Jun 15 '17 at 08:21

Siddharth Rout

147,039
17
206
250

1

Working with a whole range at once like this is certainly much faster than a cell loop. – iDevlop Jun 15 '17 at 08:24
But using Trim doesn't remove spaces in middle of the string, which is what I need too (see the example I provided). – Alluton Jun 15 '17 at 08:36
@Alluton: It does remove :) . Type "aaaaaaa aaaaaa" in cell A1 where there is lot of space between 2 words and try my code – Siddharth Rout Jun 15 '17 at 08:40
@Alluton: See the screenshot attached. You may have to refresh the page – Siddharth Rout Jun 15 '17 at 08:42
Doesn't that screenshot show that there is still one space left in between "dsasd" and "sadasd" ? – Alluton Jun 15 '17 at 08:51
You want to remove those as well? @Alluton – Siddharth Rout Jun 15 '17 at 08:52
Yes. I don't want to have any spaces left. – Alluton Jun 15 '17 at 08:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/146736/discussion-between-siddharth-rout-and-alluton). – Siddharth Rout Jun 15 '17 at 09:04

Removing spaces from cell

1 Answers1