Removing spaces is driving me nuts
 

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

Removing spaces is driving me nuts
 

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)


"Ixtreme" wrote in message
...
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

Removing spaces is driving me nuts
 

On Oct 18, 10:34*am, "Rick Rothstein"
wrote:
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)

"Ixtreme" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

Removing spaces is driving me nuts
 

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

"Ixtreme" wrote in message
...
On Oct 18, 10:34 am, "Rick Rothstein"
wrote:
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)

"Ixtreme" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

Removing spaces is driving me nuts
 

Nope, still no luck.

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

Removing spaces is driving me nuts
 

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

--
__________________________________
HTH

Bob

"Ixtreme" wrote in message
...
Nope, still no luck.

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

Removing spaces is driving me nuts
 

Info only

ASCII char 9 is a Tab char.


Gord Dibben MS Excel MVP

On Sun, 18 Oct 2009 04:02:42 -0700 (PDT), Ixtreme
wrote:

Nope, still no luck.

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

Removing spaces is driving me nuts
 

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

--
Rick (MVP - Excel)


"Ixtreme" wrote in message
...
Nope, still no luck.

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

Removing spaces is driving me nuts
 

Thanks, it seems to be ok now.