Removing spaces is driving me nuts

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
 
R

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.
 
I

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?
 
B

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?
 
B

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top