Remove all spaces from a Column c

[MarkCBB]

HI there,

I Am trying to remove all the Spaces from a column, but for some reason some of the spaces are not being removed, this is the code that I am using:

Columns("C").Replace What:=" ", Replacement:=""

All of the spaces are before the numerical value i.e. " 812" or " 6"

 

[Gerald Higgins]

I tried replicating this, and I couldn't.
Your code deleted all the spaces that I had input, whether they were before numbers or text.
Is it possible that there is some other non-printing character that is taking up space ?
Is it possible that there isn't actually a space there, but it just looks as if there is, perhaps because of indented formating or something similar ?

 

[MarkCBB]

The data is dumped from a payroll reports, When I was trying to replicate this on a new sheet bu entering the data, manually I also couldn't replicate the error. I have checked for other non printing characters but there are none.


I found this formula on another site

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

and it seems to work well. but it means I need a helper column.

 

[Gerald Higgins]

I don't think CHAR(160) is the space character.
When I input a space character, it's CHAR(32).
I'm not sure but I think 160 might be Carriage Return.

 

[MarkCBB]

It looks like its working (I have no Idea what Carriage Return is), but its removing all of the spaces now.

For i = 160 To 160
a = Replace(sh.Cells(rowID, 5), Chr(i), "")
sh.Cells(rowID, 5) = a
Next i

Strange.

 

[Gerald Higgins]

I tried recording a macro while I manually did Find and Replace.
I created a cell containing just CHAR(160), copied it, then turned on the Macro Recorder, did Edit, Find and Replace, pasted into the Find Box (it looked blank but there was something in there) and replaced it with the letter a, and stopped the recorder.

The code I got looked just like your original.
BUT, the space character in the code must have been different, because when I ran the code on some sample data, it deleted the CHAR(160)s and left the genuine spaces.

There must be a neater way of writing this code, and I'm sorry I don't know what it is, but I did get it to work.

Does this make sense ?

 

[Gerald Higgins]

Carriage Return is what you used to do on an old manual typewriter, to start a new line.
You can use this character to force text to start on a new line - for example within a single Excel cell (you might need to format the cell to allow text wrapping first).

 

[JD_GFI]

Try

[FONT=Arial][SIZE=2][COLOR=#000000]Selection.Replace what:=Chr(160), replacement:=""[/COLOR][/SIZE][/FONT]

chr(160) is a non-breaking space.

 

[Gerald Higgins]

Thanks JD GFI. On further investigation, I think Carriage Return is actually CHAR(10), but the general point I think is correct, that Mark's spaces were somehow different from "normal" spaces.

 

[JD_GFI]

Aye Gerald If you combine the original code with my suggestion it should eliminate all the niggling wee beasties.