Getting rid of extra spaces at the end

[josephemery]

Hey hopefully someone can help me. I am trying to read some data that I cut and paste in from a database into a excel sheet. Base on the data outfall I want it to go do specfic sheets. But the problem is that the data has a extra space on the end from the database. I tried Trim,RTrim ,and LTrim, but for some reason the spaces is still there. well here the code.

Sub readcolumn(col,number)
Dim i As Variant
Dim j as Variant
i = 1
j = number
j =1
Do While cells.(i,j)<>""
col(i) = CVar(col(i))
col(i) = trim(col(i))
End If
i = i + 1
Loop
End Sub


After I run this the space is still there and with the space excel can't see it the same one as the other on with space and dosent put it where i need it. So if anybody can help,I would be greatful, Thanks. I attaced the a column with the data if that help hopefully it will. Thanks bye.

[mdmackillop]

Hi Joseph,
The trailing character is ascii 160. Try =SUBSTITUTE(C1,CHAR(160),"") to get rid of it.
Regards
MD

[josephemery]

MD,
Thanks, I tried that but I never used the SUBSTITUTE command and I try to cut in past it in and getting that the "CHAR" is not defined. It may be a dumb question but for some reason i cant get it to work. I looked around and I cant see my mistake.

Joseph

[Bob Phillips]

MD,
Thanks, I tried that but I never used the SUBSTITUTE command and I try to cut in past it in and getting that the "CHAR" is not defined. It may be a dumb question but for some reason i cant get it to work. I looked around and I cant see my mistake.

Joseph

That is a worksheet function, not VBA.

[mdmackillop]

Try

Sub readcolumn()
Dim i As Long
i = 1
Do While Cells(i, 3) <> ""
Cells(i, 1) = WorksheetFunction.Substitute(Cells(i, 3), Chr(160), "")
Cells(i, 2) = Len(Cells(i, 1))
Cells(i, 4) = Len(Cells(i, 3))
i = i + 1
Loop
End Sub

[Ken Puls]

Hi there,

You might also try the Clean function. Modifying MD's code (untested by myself)...


Sub readcolumn()
Dim i As Long
i = 1
Do While Cells(i, 3) <> ""
Cells(i, 1) = WorksheetFunction.Clean(Cells(i, 3))
Cells(i, 2) = Len(Cells(i, 1))
Cells(i, 4) = Len(Cells(i, 3))
i = i + 1
Loop
End Sub

HTH,

[mdmackillop]

Hi Ken
Chr(160) is a nasty little thing, not dealt with by Clean or Trim. It caused me a few headaches until I knew to look for it.
Regards
Malcolm

From Help


Remove spaces and nonprinting characters from text

Sometimes text values contain leading, trailing, or multiple embedded space characters (Unicode character set (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) (http://javascript<b></b>:AppendPopup(this,'ofUnicode_1')) values 32 and 160), or non-printing characters (Unicode character set values 0 to 31, 127, 129, 141, 143, 144, and 157). These characters can sometimes cause unexpected results when you sort, filter, or search. For example, users may make typographical errors by inadvertently adding extra space characters, or imported text data from external sources may contain nonprinting characters embedded in the text. Because these characters are not easily noticed, the unexpected results may be difficult to understand. To remove these unwanted characters, you can use a combination of the TRIM, CLEAN, and SUBSTITUTE functions.

=TRIM(A2) Removes the trailing space from the string "BD 122 " (BD 112)

=CLEAN(A3) Removes the nonprinting BEL character (ASCII value of 7) from the string value created by the expression ="XY"&CHAR(7)&"453" (XY453)

=TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32))) Replaces each nonbreaking space character (Unicode value of 160) with a space character (ASCII value of 32) by using the SUBSTITUTE function, and then removes the leading and multiple embedded spaces from the string " BD 122" (BD 112)

=CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL character (ASCII value of 127) with a BEL character (ASCII value of 7) by using the SUBSTITUTE function, and then removes the BEL character from the string "MN987" (MN987)

[Ken Puls]

Ah! Good to know, Malcolm. I've always had success with Clean on my apps in the past, so obviously didn't get hit with that one.

Thanks for the info!

[josephemery]

Thanks Guys,

You help me out alot, you guys ROCK!! and I really like to thank MD for the explainations, they made it alot clearer of what the code does. Well thanks again.

[mdmackillop]

Hi Joseph,
Happy to help.
If your question is solved, will you mark it so using the Thread Tools menu.
Regards
MD

Edit:
Here's a wee utility to check for these things!

Sub Chars()
For i = 1 To Len(ActiveCell)
msg = msg & Mid(ActiveCell, i, 1) & vbTab & _
Asc(Mid(ActiveCell, i, 1)) & vbCr
Next
MsgBox msg
End Sub