Leading Spaces - something odd ?

[stuartgb100]

Hi,

I have some code from this site that removes leading spaces from most cells in my data, but not all.

What sort of thing could cause these 'unknown' spaces and how do i deal with them please ?

Here's the code:

Sub RemoveLeadingTrailingSpaces()
' hiker95, 02/26/2016, ME9242394
With ActiveSheet
With .Range("C8:C24")
.Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
End With
End Sub

Thanks.

 

[Fluff]

Try

Sub CleanSpaces()
   Range("C8:C24").Replace Chr(160), "", xlPart, , , , False, False
End Sub

 

[Joe4]

If the data has come from export from other systems, or from web downloads, they often have other "special" characters, such as "non-breaking spaces", which are different from regular spaces.

The first thing I usually like to do is identify exactly what it is I am working with. I do this by identifying a problem records, and returning the ASCII value of the character in that space.
For example, let's say I have some weird character in the first space of an entry in cell A1. I can use this formula to get the ASCII value of that character:

=CODE(LEFT(A1,1))

Then I can use an ASCII table to look up what character that number represents:
http://www.asciitable.com/

 

[stuartgb100]

Fluff,

Thanks that worked.
I will do a search on chr(160).

Joe4,
Thanks for the background info.
I will do some reading !

Regards

 

[Rick Rothstein]

Fluff,

Thanks that worked.
I will do a search on chr(160).

If your range is fixed (like your post suggests it is), then you can handle the non-breaking spaces and the trim operation all in one macro...

[table="width: 500"]
[tr]
	[td]Sub RemoveLeadingTrailingSpaces()
  Range("C8:C24") = Evaluate(Replace("IF(@="""","""",TRIM(SUBSTITUTE(@,CHAR(160),"" "")))", "@", "C8:C24"))
End Sub[/td]
[/tr]
[/table]