AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
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 :
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 :
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
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 :
Code:
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