trimming whitespace

  • Thread starter Thread starter Jamie Martin
  • Start date Start date
J

Jamie Martin

Can I trim all whitespace from a string, turning "cat<any number of spaces,
plus if possible any number of tabs>dog" 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(sStr1," ",""),chr(9),"")
catdog
 
Jamie,

Try this formula-

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

regards,

JohnI
 
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
 
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
 
Back
Top