Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing spaces is driving me nuts
Nope, still no luck.
? Asc(Right(Range("A1").Value, 1)) returns a 9. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing spaces is driving me nuts
Thanks, it seems to be ok now.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | New Users to Excel | |||
question driving me nuts | Excel Discussion (Misc queries) | |||
Driving me nuts. Need more nested than 7 | Excel Discussion (Misc queries) | |||
Excel / VB is driving me nuts!! | Excel Worksheet Functions |