Removing spaces is driving me nuts

[Ixtreme]

I have an imported sheet with data. It contains various lists that I
use in a dropdown box. However, since many entries have additional
spaces at the end, the dropdown box shows the same item mutliple
times. I have tried, trim, clean but still no luck.

If I do a code() I get 32. If I look in vba I see that the 2 spaces
are displayed as 2 little squares.

I would like a piece of code that loops through all used cells per
column and then removes the additional spaces (sometimes, 2, 3 or even
4).

I found this on the net, but that does not work:

Public Function superTrim(TheString As String) As String

Dim TemP As String, DoubleSpaces As String

DoubleSpaces = Chr(32) & Chr(32)
TemP = Trim(TheString)
TemP = Replace(TemP, DoubleSpaces, Chr(32))
Do Until InStr(TemP, DoubleSpaces) = 0
TemP = Replace(TemP, DoubleSpaces, Chr(32))
Loop
superTrim = TemP

 

[Rick Rothstein]

If VB is displaying them as "little squares", then they are not spaces. To
find out what they are, execute this in the Immediate Window for any one of
the cells (assuming A1 for the sample code line below)...

? Asc(Right(Range("A1").Value, 1))

This will tell you the ASCII value of the last character in the cell.

 

[Ixtreme]

If VB is displaying them as "little squares", then they are not spaces. To
find out what they are, execute this in the Immediate Window for any one of
the cells (assuming A1 for the sample code line below)...

? Asc(Right(Range("A1").Value, 1))

This will tell you the ASCII value of the last character in the cell.

--
Rick (MVP - Excel)












- Show quoted text -

Thanks for answering. It is returning a 9. So what should I do next?

 

[Bob Phillips]

Try this

Public Function superTrim(TheString As String) As String
Dim TemP As String, DoubleSpaces As String

DoubleSpaces = Chr(32) & Chr(32)
TemP = Trim(TheString)
TemP = Replace(TemP, DoubleSpaces, Chr(32))
Do Until InStr(TemP, DoubleSpaces) = 0
TemP = Replace(TemP, DoubleSpaces, Chr(32))
Loop
superTrim = Replace(TemP, Chr(160), Chr(32))
End Function


--
__________________________________
HTH

Bob

If VB is displaying them as "little squares", then they are not spaces. To
find out what they are, execute this in the Immediate Window for any one
of
the cells (assuming A1 for the sample code line below)...

? Asc(Right(Range("A1").Value, 1))

This will tell you the ASCII value of the last character in the cell.

--
Rick (MVP - Excel)












- Show quoted text -

Thanks for answering. It is returning a 9. So what should I do next?

 

[Ixtreme]

Nope, still no luck.

? Asc(Right(Range("A1").Value, 1)) returns a 9.

 

[Bob Phillips]

Okay, try this then

Public Function superTrim(TheString As String) As String
Dim TemP As String, DoubleSpaces As String

DoubleSpaces = Chr(32) & Chr(32)
TemP = Trim(TheString)
TemP = Replace(TemP, DoubleSpaces, Chr(32))
Do Until InStr(TemP, DoubleSpaces) = 0
TemP = Replace(TemP, DoubleSpaces, Chr(32))
Loop
superTrim = Replace(TemP, Chr(160), " ")
superTrim = Replace(TemP, Chr(9)," ")

End Function

 

[Gord Dibben]

Info only

ASCII char 9 is a Tab char.


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

Give this function a try (copy/paste it so you don't miss the double
spaces)...

Function BigTrim(S As String) As String
BigTrim = Replace(Replace(S, Chr$(9), " "), Chr$(160), " ")
Do While InStr(BigTrim, " ")
BigTrim = Replace(BigTrim, " ", " ")
Loop
BigTrim = Trim(BigTrim)
End Function

 

[Ixtreme]

Thanks, it seems to be ok now.