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.

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.

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

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 wrote:

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

--

Dave Peterson

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

Greg

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 wrote:

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

Greg

--

Dave Peterson

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

"Jim Thomlinson" wrote:

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.

Hi quartz

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"quartz" wrote in message ...
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.

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
on to this excellent resource!

"Ron de Bruin" wrote:

Hi quartz

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"quartz" wrote in message ...
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.