String Data Cleanup 1

[MasterRacker]

I have data extracted from another application that I need to cleanup by trimming it and removing embedded newlines. This code works just fine

Public Function FixString(orig As String) As String
On Error GoTo Err_Handler
Dim newLine, FixedStr As String
    newLine = Chr(13) & Chr(10) 
    FixedStr = Left(orig, 130)
    FixedStr = Replace(FixedStr, newLine, "|")

I don't have full info on what else might be in the string data and would like to make this more robust.

I would like to strip everything out of the string except ASCII 32-126. I could loop through and make a character by character copy, filtering what I don't want, but I'm looking for a "learning experience" if someone can suggest a more efficient/elegant method.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Remou]

How about Regular Expressions?

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True

'Test string
strText = "q@12""£c"

'Find anything not a-z, 0-9
objRegEx.Pattern = "[^a-z0-9]"

'And replace with -
strOut = objRegEx.Replace(strText, "-")

More Info:

http://msdn.microsoft.com/en-us/library/ms974570.aspx

 

[MasterRacker]

I've used regex in C#, but thinking in "VBA mode", I hadn't even thought of them. I like it. Thanks.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]