You can give this a try...

Sub test()
Dim str As String

str = "This Sentence " & vbCrLf & _
" Needs to be " & vbTab & "cleaned."
MsgBox str
Call CleanString(str)
MsgBox str
End Sub

Public Sub CleanString(ByRef str As String)
str = Replace(str, vbCrLf, " ")
str = Replace(str, vbTab, " ")
Do While InStr(str, " ") 0
str = Replace(str, " ", " ") 'remove double spaces
Loop
End Sub
--
HTH...

Jim Thomlinson


"quartz" wrote:

I am using Office 2003 on Windows XP.

I have a program that creates a CSV file and currently, along with TRIM, I'm
using:

Application.WorksheetFunction.Clean(sData)

to clean out unprintable characters (like carriage returns) from the
variable contents before writing it into the CSV file. I have two questions:

1. Is there an equivalent VBA function to CLEAN that I could/should be using?

2. In some of the text descriptions of the source data there are sometimes
multiple consecutive tabs, blank spaces (spacebar) and other undesirable
characters, that not even CLEAN and TRIM remove. Is there another function I
can use to help me clean up this sort of thing, or does someone have a
cleaning function they can share for this type of thing? Any suggestions
welcomed.

Thanks much in advance.