CODEWITHSUNDEEP
vbaexcel
Alluton
Alluton

Reputation: 170

Removing spaces from cell

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?

Upvotes: 1

Views: 19512

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

enter image description here

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.

enter image description here

Upvotes: 3

Related Questions