Can't seem to remove non printable characters

[carsto]

Outlook 2010
I can't control what the user actually types. They're using a template so if they alter it and I can't find the info they'll get irritating pop up messages, but users will do as they wish.

The BODY is something like this:

Blah blah blah blah blah several rows and returns

Title: [tab] The Title [return]
[tab] [tab] More Info


myStr using INSTR() to find "The Title"
myStr = Mid(Item.Body, a, b)
myStr might equal something like " The Title [return] [tab] "

NONE of the FOLLOWING rid me of the non-printable characters
myStr = Replace(myStr, Chr(9), "")
myStr = Replace(myStr, Chr(10), "")
myStr = Replace(myStr, Chr(13), "")
myStr = Replace(myStr, Chr(16), "")
myStr = Replace(myStr, vbLf, "")
myStr = Replace(myStr, vbCrLf, "")
myStr = Replace(myStr, vbNewLine, "")


I'm using myStr in a SQL statement.

[Glenn Kennedy]

I know nothing about VBA. However, in situations like this, I try to run the problem to ground using this formula:

=CODE(MID($A$1,COLUMNS($A:A),1))

copied across. I can then identify "problem" characters and deal with them. this way, you'll be able to see the codes of all caharacters and identify what's happening (hopefully)...

[kev_]

This may help you identify the characters causing your problem

Please Login or Register  to view this content.

[carsto]

Thanks Kev_

Chr 160 non-breaksing space and
Chr 32 space

interesting that TRIM() didn't work but replace Chr 32 did!

[kev_]

you are welcome