Trim function not removing leading space (XL2007)

[AOB]

Hi,

I have a piece of code that takes a value from a cell, checks to make sure it is 8 characters long and prompts an error if not.

To handle situations where the cell has not been 'properly' populated (i.e. with 'rogue' characters such as " ", "-", "." or "/"), I use Trim() and Replace() to reduce the cell value down to what should be the core 8 characters :

If Not Len(Trim(Replace(Replace(Replace(Replace( & _[INDENT]wsSheet.Cells(i, j).Value, " ", ""), "-", ""), "/", ""), ".", ""))) = 8 Then[/INDENT]
[INDENT=2]' Prompt error handler etc.[/INDENT]
End If

This has worked fine for months but I've just found an example where it doesn't, and I can't figure out why. When debugging, the cell value appears to have a rogue leading space :

wsSheet.Cells(i, j).Value = " ABCDEFGH"

Which I would expect either the Trim() or the first Replace() functions to take care of. But when I evaluate the result of the Len() argument, that rogue leading space is still there?

Which suggests to me that either a) the Trim and Replace functions aren't working (hardly) or, b) that first character isn't actually a space but some other character which just looks like a space in the debugger (and, incidentally, on the sheet itself)

Any suggestions as to what the problem might be, or how I can identify exactly what this rogue character is??

Thanks

AOB

 

[Firefly2012]

Hi

Sounds like the leading space is actually a non-breaking space which is ASCII character 160. You can use Replace to get rid of this (replace Chr$(160) with "")

 

[AOB]

Thanks Firefly

I'll give that a go - just out of curiosity, how would a user populate such a character into a cell on a worksheet? Just trying to quantify the likelihood of this happening again...

 

[RoryA]

Copy and paste from a website most likely.

 

[Firefly2012]

Well, nbsp are commonly used in web-pages as browsers don't 'break' the flow of text on these characters compared to normal spaces (hence the name) so if a user was to copy and paste some text from a website then the pasted text could well include nbsp.

Other ways would be to use Alt+0160 on the numeric keypad (Alt+255 on many Western operating systems).

Also you can create one using an excel formula:

=CHAR(160)

My money would be on pasting web text though

 

[AOB]

Thanks Rory & Firefly2012

Looks like a copy & paste origin alright...

Just as an FYI for anyone else hitting a similar problem, I put together a specialised Trim function which specifically removes any non-printing ASCII characters as well as leading / trailing spaces, with an optional parameter for any additional characters which can be defined when calling the function

Function SuperTrim(stOriginal As String, Optional arToBeRemoved As Variant) As String
[INDENT]
Dim i As Long
Dim j As Long
Dim stChar As String
Dim stTemp As String

For i = 1 To Len(stOriginal)


[/INDENT]
[INDENT=2]stChar = Mid(stOriginal, i, 1)

Select Case Asc(stChar)
[/INDENT]
[INDENT=3]
Case Is < 32    ' Non-printing characters[/INDENT]
[INDENT=3]
Case 160        ' Non-breaking carriage return[/INDENT]
[INDENT=3]
Case Else       ' All other characters in ASCII alphabet[/INDENT]
[INDENT=4]
If Not IsMissing(arToBeRemoved) Then


[/INDENT]
[INDENT=5]For j = LBound(arToBeRemoved) To UBound(arToBeRemoved)


[/INDENT]
[INDENT=6]If stChar = arToBeRemoved(j) Then GoTo SkipThisCharacter[/INDENT]
[INDENT=5]
Next j


[/INDENT]
[INDENT=4]End If

stTemp = stTemp & stChar


[/INDENT]
SkipThisCharacter:[INDENT=2]
End Select[/INDENT]
[INDENT]
Next i

SuperTrim = Trim(stTemp)[/INDENT]

End Function

Called thusly :

stExample1 = SuperTrim(wsSheet.Cells(1, 1).Value)
' Remove non-printing ASCII characters + leading / trailing spaces

stExample2 = SuperTrim(wsSheet.Cells(1, 1).Value, Array(".","-"," "))
' Also remove periods, hyphens, inside spaces etc.