Clean up strings before creating a text file

[quartz]

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.

 

[Jim Thomlinson]

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

 

[Ron de Bruin]

Hi quartz

Use David's macro to do this
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[GregR]

JIm, I also have strings in a range (F2:F200) that need cleaning for
two instances. If the string contains (__________________) parenthesis,
I need everything in the parenthesis removed as well as the space
before and the parenthesis themselves. Instance 2, if the string
contains a comma, I need everything after and including the comma
removed. TIA

Greg

 

[quartz]

Thanks Jim. I like the idea of having a separate clean up function, I think I
will adapt your code...thanks!

 

[quartz]

He's got a lot of great info there, in particular, I didn't know about the
difference between the VBA TRIM and the TRIM function. Thanks for putting me

 

[Dave Peterson]

I think I would have used a function--but with some minor modifications to Jim's
code:

Option Explicit
Sub test()
Dim Str As String

Str = "This Sentence y(this portion, too)x " & vbCrLf & _
" Needs, to be " & vbTab & "cleaned."
MsgBox Str
Str = CleanString(Str)
MsgBox "|" & Str & "|" '<-- just to see it nicely
End Sub

Function CleanString(Str As String) As String
Dim InParens As Boolean
Dim tStr As String
Dim iCtr As Long
Dim CommaPos As Long

Str = Replace(Str, vbCrLf, " ")
Str = Replace(Str, vbTab, " ")

CommaPos = InStr(1, Str, ",", vbTextCompare)
If CommaPos > 0 Then
Str = Left(Str, CommaPos - 1)
End If

InParens = False
tStr = ""
For iCtr = 1 To Len(Str)
If Mid(Str, iCtr, 1) = "(" Then
InParens = True
'don't add the character to tStr
ElseIf Mid(Str, iCtr, 1) = ")" Then
InParens = False
'don't add the character to tStr
ElseIf InParens = False Then
tStr = tStr & Mid(Str, iCtr, 1)
End If

Next iCtr

CleanString = Application.Trim(tStr)

End Function

 

[GregR]

If I want to clean a range of cells do I set the str =
Range("A1:A100")?

Greg

 

[Dave Peterson]

You could do it a couple of different ways.

#1. Use an adjacent cell and put:
=CleanString(a1)
and copy down

#2. You could make the macro look at each of those cells.


Sub test()
dim myRng as range
dim myCell as range

set myrng = activesheet.range("a1:A100")
for each mycell in myrng.cells
mycell.value = cleanstr(mycell.value)
next mycell
End Sub

(Watch out for typos. I composed in the email.)

 

[GregR]

Dave, thank you very much again