Removing spaces is driving me nuts

  • Thread starter Thread starter Ixtreme
  • Start date Start date
I

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
 
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.
 
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 -
Click to expand...

Thanks for answering. It is returning a 9. So what should I do next?
 
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 -
Click to expand...

Thanks for answering. It is returning a 9. So what should I do next?
 
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
 
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
 
Back
Top