Can I trim all whitespace from a string, turning "cat<any number of spaces,
plus if possible any number of tabsdog" into "catdog"?

sStr = Application.Substitute(Application.Substitute(str1 ," ",""),chr(9),"")

demo'd from the immediate window:

sStr1 = "cat " & vbTab & " " & vbTab & vbTab & " " & vbtab & "dog"
? sStr1
cat dog
? Application.Substitute(Application.Substitute(sStr 1," ",""),chr(9),"")
catdog

--
Regards,
Tom Ogilvy




"Jamie Martin" wrote in message
...
Can I trim all whitespace from a string, turning "cat<any number of
spaces,
plus if possible any number of tabsdog" into "catdog"?

Jamie,

Try this formula-

=SUBSTITUTE(SUBSTITUTE(D2,CHAR(9),"")," ","")

regards,

JohnI

"Jamie Martin" wrote in message
...
Can I trim all whitespace from a string, turning "cat<any number of
spaces,
plus if possible any number of tabsdog" into "catdog"?

Jamie,

this is my trim function. Just add space in the list (you should not be able
to have tabs within a string, just spaces). It should do the job. It also
takes in a parameter if you want to cut the string after a certain number of
characters (charts having more than 32 characters in the name of it start to
behave oddly, that's why I have it).

HTH,
Jouni
Finland

Function TrimString(strString As String, intLength As Integer) As String
' removes illegal characters for worksheets and charts.
' sets length as passed.
Dim intChar As Integer
For intChar = 1 To Len(strString)
If Mid(strString, intChar, 1) < "/" And Mid(strString, intChar, 1)
< "\" And _
Mid(strString, intChar, 1) < "*" And Mid(strString, intChar, 1)
< "?" And _
Mid(strString, intChar, 1) < ":" And Mid(strString, intChar, 1)
< " " And _
Mid(strString, intChar, 1) < "," And Mid(strString, intChar, 1)
< "." And _
Mid(strString, intChar, 1) < "(" And Mid(strString, intChar, 1)
< ")" And _
Mid(strString, intChar, 1) < "[" And Mid(strString, intChar, 1)
< "]" And _
Mid(strString, intChar, 1) < "-" And Mid(strString, intChar, 1)
< "&" And _
Mid(strString, intChar, 1) < "'" And Mid(strString, intChar, 1)
< ";" And _
Mid(strString, intChar, 1) < "_" And Mid(strString, intChar, 1)
< "anything_crap" Then
TrimString = TrimString & Mid(strString, intChar, 1)
End If
Next
TrimString = Left(TrimString, intLength)
End Function



"Jamie Martin" wrote in message
...
Can I trim all whitespace from a string, turning "cat<any number of
spaces,
plus if possible any number of tabsdog" into "catdog"?

Just another similar option:

Sub Demo()

Dim s As String
s = "cat " & vbTab & " " & vbLf & vbTab & " " & vbCrLf & "dog"
s = Replace(WorksheetFunction.Clean(s), Space(1), vbNullString)

'// or
s = "cat " & vbTab & " " & vbLf & vbTab & " " & vbCrLf & "dog"
With WorksheetFunction
s = .Substitute(.Clean(s), Space(1), vbNullString)
End With

End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Jamie Martin" wrote in message
...
Can I trim all whitespace from a string, turning "cat<any number of
spaces,
plus if possible any number of tabsdog" into "catdog"?