Reputation: 1195

Trim all types of whitespace, including tabs

In VB6, the Trim() function trims spaces off the front and back of a string. I am wondering if there is a function that will trim not just spaces, but all whitespace (tabs in this case) off of each end of a string.

Upvotes: 12

Answers (12)

member300

Reputation: 1

The simplest solution I can think of and also work with empty strings which some examples here don't do.

Const cXTrim As String = " " & vbTab & vbCr & vbLf & bVerticalTab & _
  vbFormFeed & vbNullChar
    

Public Function XLTrim(sText) As String

  Dim i As Integer
  For i = 1 To Len(sText)
    If InStr(cXTrim, Mid(sText, i, 1)) = 0 Then Exit For
  Next
  XLTrim = Mid$(sText, i)

End Function

Public Function XRTrim(sText) As String

  Dim i As Integer
  For i = Len(sText) To 1 Step -1
    If InStr(cXTrim, Mid(sText, i, 1)) = 0 Then Exit For
  Next
  XRTrim = Mid$(sText, 1, i)

End Function

Public Function XTrim(sText) As String
  Dim i As Integer
  Dim k As Integer
   
  For i = 1 To Len(sText)
    If InStr(cXTrim, Mid(sText, i, 1)) = 0 Then Exit For
  Next

  For k = Len(sText) To i Step -1
    If InStr(cXTrim, Mid(sText, k, 1)) = 0 Then Exit For
  Next
  
  XTrim = Mid$(sText, i, k - i + 1))

End Function

Upvotes: 0

Jan

Reputation: 2249

Had some problems with new line from XL cell made a bit more general

Private Function TrimAll(Text As String) As String

Dim s As Long: s = 1
Dim e As Long: e = Len(Text)
Dim c As String

If e = 0 Then Exit Function 'zero len string

For s = 1 To e
    If Mid(Text, s, 1) <= " " Then c = c & " "
    While s < e And Mid(Text, s, 1) <= " "
        s = s + 1
    Wend
    c = c & Mid(Text, s, 1)
Next
TrimAll = c
End Function

Mind this simple function does not trim start nor end of string, but now even TABs are replaced (XL fakes them as spaces), also hard space not tested (will be >32 probably).

Upvotes: 0

Safwan

Reputation: 360

I have combined the functions from my previous answer into a single function that will trim any leading and/or trailing whitespace (spaces and tabs) from a string and optionally any characters that are passed in the second parameter of the function.

To trim just the whitespace, pass an empty string into the second parameter.

I often use this function to trim strings that contain paths to files or folders by calling the function like this:

NewValue = Clip(FilePath, """'")

The above example will strip any leading or trailing whitespace but only balanced double quotes or single quotes. This means that quotes will only be trimmed if the string begins AND ends with one.

Here is the function:

Private Function Clip(ByVal strString, ByVal strChars) 'As String

  Dim x, y
  Dim NewString, Char
  ReDim Flag(Len(strChars)+1)

  Do

   NewString=strString : Flag(0)=0

   Do
    NewString = Trim(NewString)
    If Left(NewString, 1) = vbTab Then NewString = Mid(NewString, 2)
    If Right(NewString, 1) = vbTab Then NewString = Left(NewString, Len(NewString) - 1)
   Loop While (Left(NewString, 1) = " ") Or (Right(NewString, 1) = " ")

   If strString=NewString Then Flag(1)=0 Else Flag(1)=1 : strString=NewString

   For y = 1 To Len(strChars)

    NewString=strString : Char=Mid(strChars, y, 1)

    Do While (Left(NewString, 1) = Char) And (Right(NewString, 1) = Char)
     NewString = Mid(NewString, 2, Len(NewString) - 2)
    Loop

    If strString=NewString Then Flag(y+1)=0 Else Flag(y+1)=1 : strString=NewString

   Next

   For x = 1 To UBound(Flag)
    Flag(0) = Flag(0) + Flag(x)
   Next

  Loop Until Flag(0)=0

  Clip = strString

End Function 'Clip

Upvotes: 0

Safwan

Reputation: 360

This is another approach that consists of 3 functions:

TrimWS
TrimChar
TrimAll

The first 2 functions can be used independently. The third function (TrimAll) demonstrates how to combine the first 2 to include any custom characters to be trimmed from a string.

TrimWS: Removes all leading/trailing spaces and tabs.

Function TrimWS(ByVal str)

 Do

  str = Trim(str)
  If  Left(str, 1) = vbTab Then str = Mid(str, 2)
  If Right(str, 1) = vbTab Then str = Left(str, Len(str)-1)

 Loop While (Left(str, 1)=" ") Or (Right(str, 1)=" ")

 TrimWS = str

End Function

TrimChar: Removes all leading/trailing characters which equals to the character passed as parameter.

Function TrimChar(ByVal str, ByVal char)

 Do While (Left(str, 1)=char) And (Right(str, 1)=char)
  str = Mid(str, 2, Len(str)-2)
 Loop

 TrimChar = str

End Function

TrimAll: Removes all leading/trailing spaces, tabs, and any other character specified. In the example below, it will strip any spaces, tabs, double quotes ("), and single quotes (') from a string.

Note: If you are going to add or remove characters from TrimAll, remember to modify the line Dim Flag(3) As Integer accordingly.

Function TrimAll(ByVal str)

 Dim OldValue As String
 Dim NewValue As String
 Dim Flag(3)  As Integer
 Dim x        As Integer

 Do

  Flag(0) = 0

  OldValue = str : NewValue = TrimWS(str)
  If OldValue = NewValue Then Flag(1) = 0 Else Flag(1) = 1 : str = NewValue

  OldValue = str : NewValue = TrimChar(str, """")
  If OldValue = NewValue Then Flag(2) = 0 Else Flag(2) = 1 : str = NewValue

  OldValue = str : NewValue = TrimChar(str, "'")
  If OldValue = NewValue Then Flag(3) = 0 Else Flag(3) = 1 : str = NewValue

  For x = 1 To UBound(Flag)
   Flag(0) = Flag(0) + Flag(x)
  Next

 Loop Until Flag(0)=0

 TrimAll = str

End Function

Upvotes: 0

felix

Reputation: 1

better not forget to iterate the function it self as there might be a sequence of tabs whitspaces and line breaks in not ordered manner and you would like to clean all of them.

"tab & space & tab & tab & linebreak & space & tab & linebrea ...."

Upvotes: -1

Ovidiu Luca

Reputation: 91

I use this function:

Private Function TrimAll(Text As String) As String

Const toRemove As String = " " & vbTab & vbCr & vbLf 'what to remove

Dim s As Long: s = 1
Dim e As Long: e = Len(Text)
Dim c As String

If e = 0 Then Exit Function 'zero len string

Do 'how many chars to skip on the left side
    c = Mid(Text, s, 1)
    If c = "" Or InStr(1, toRemove, c) = 0 Then Exit Do
    s = s + 1
Loop
Do 'how many chars to skip on the right side
    c = Mid(Text, e, 1)
    If e = 1 Or InStr(1, toRemove, c) = 0 Then Exit Do
    e = e - 1
Loop
TrimAll = Mid(Text, s, (e - s) + 1) 'return remaining text

End Function

Usage:

    Debug.Print "|" & TrimAll("") & "|" 'prints ||
    Debug.Print "|" & TrimAll(" ") & "|" 'prints ||
    Debug.Print "|" & TrimAll("a") & "|" 'prints |a|
    Debug.Print "|" & TrimAll("a ") & "|" 'prints |a|
    Debug.Print "|" & TrimAll(" a") & "|" 'prints |a|
    Debug.Print "|" & TrimAll(" a b ") & "|" 'prints |a b|
    Debug.Print "|" & TrimAll(vbTab & " " & "Some " & vbCrLf & " text. " & vbCrLf & " ") & "|" 'prints |Some
text.|

You can simply add the chars to be removed at the toRemove string.

It does not copy the partialy trimmed string again and again, but rather searches where the trimmed string starts and ends, and returns that portion only.

Upvotes: 8

Gordon Smith

Reputation: 51

For vb.net (very similar) to remove all whitespace and control characters from front:

Public Function TrimWspFromFront(ByRef MyStr As String) As String
    While MyStr.Length > 0 AndAlso Left(MyStr, 1) < " "
        MyStr = Trim(Right(MyStr, MyStr.Length - 1))
    End While
    Return MyStr
End Function

To remove from rear:

Public Function TrimWspFromEnd(ByRef MyStr As String) As String
    While MyStr.Length > 0 AndAlso Right(MyStr, 1) < " "
        MyStr = Trim(Left(MyStr, MyStr.Length - 1))
    End While
    Return MyStr
End Function

NB. Passed as ByRef to avoid overhead of making a copy, others may prefer to code as a Sub or pass ByVal

Upvotes: 1

Affiliate Switchblade

Reputation: 19

Here is something I came up with that lets you choose between returning the trimmed string itself or the length of the trimmed string

in a module

'=========================================================
'this function lets get either the len of a string with spaces and tabs trimmed of
'or get the string itself with the spaces and tabs trimmed off
'=========================================================
Public Property Get eLen(sStr As String, Optional bTrimTabs As Boolean = True, Optional bReturnLen As Boolean = True) As Variant

'function which trims away spaces and tabs (if [bTrimTabs] is set to True)
Dim s As String:             s = sfuncTrimEnds(sStr, bTrimTabs)

If bReturnLen Then ' if [bReturnLen] = True then return the trimmed string len
       eLen = Len(s)

Else ' if [bReturnLen] = False then return the trimmed string
       eLen = s
End If

End Property

'===============================================================
' this function trims spaces from both sides of string and tabs if  [bTrimTabs] = true
' the return value is the string with the spaces (and tabs) trimmed off both sides
'===============================================================
Private Function sfuncTrimEnds(ByVal sStr As String, Optional bTrimTabs As Boolean = True) As String



Dim lStart As Long, lEnd As Long
Dim sChr As String

Dim llen As Long:             llen = Len(sStr)
Dim l As Long:                For l = 1 To llen
                                            sChr = Mid$(sStr, l, 1)

                                            If sChr <> " " And sChr <> vbTab Then
                                                                 lStart = l
                                                                 Exit For
                                            End If
                               Next l

                               For l = llen To 1 Step -1
                                                   sChr = Mid$(sStr, l, 1)

                                                   If sChr <> " " And sChr <> vbTab Then
                                                                 lEnd = l
                                                                 Exit For
                                                   End If
                               Next l

                               sStr = Mid$(sStr, lStart, (lEnd -     (lStart - 1)))

sfuncTrimEnds = sStr

End Function

To use this:

Dim s As String:         s = "   " & vbTab & " " & "mary wants my little lamb  " & "   " & vbTab & " "
MsgBox Tru.eLen(s, , False) 'will return the trimmed text
MsgBox Tru.eLen(s)   ' will return the len of the trimmed text

       Dim sVal As String:            sVal = Tru.eLen(s, , False)
                                      if len(sval) > 0 then ' yada yada

Upvotes: 1

Fabrice T

Reputation: 663

This could also be useful, a continue of @MathewHagemann It remove empties lines before and after

Public Function TrimAllWhitespace(ByVal str As String)

    str = Trim(str)

    Do Until Not Left(str, 1) = Chr(9)
        str = Trim(Mid(str, 2, Len(str) - 1))
    Loop

    Do Until Not Right(str, 1) = Chr(9)
        str = Trim(Left(str, Len(str) - 1))
    Loop

    Do Until Not Left(str, 1) = Chr(13)
        str = Trim(Mid(str, 2, Len(str) - 1))
    Loop

    Do Until Not Left(str, 1) = Chr(10)
        str = Trim(Mid(str, 2, Len(str) - 1))
    Loop

    Do Until Not Right(str, 1) = Chr(10)
        str = Trim(Left(str, Len(str) - 1))
    Loop

    Do Until Not Right(str, 1) = Chr(13)
        str = Trim(Left(str, Len(str) - 1))
    Loop

    TrimAllWhitespace = str

End Function

Upvotes: 1

C-Pound Guru

Reputation: 16368

You'll have to combine the Trim function with the Replace function:

s = "   ABC  " & vbTab & "   "
MsgBox Len(s)

MsgBox Len(Trim$(s))

s = Replace$(Trim$(s), vbTab, "")
MsgBox Len(s)

Note: The above code will also remove embedded tabs. Probably can resolve this with regular expressions but here's a way to trim spaces/tabs only from the ends via looping:

Dim s As String, char As String, trimmedString As String
Dim x As Integer

s = "  " & vbTab & " ABC  " & vbTab & "a   " & vbTab

'// Trim all spaces/tabs from the beginning
For x = 1 To Len(s)
    char = Mid$(s, x, 1)
    If char = vbTab Or char = " " Then
    Else
        trimmedString = Mid$(s, x)
        Exit For
    End If
Next
'// Now do it from the end
For x = Len(trimmedString) To 1 Step -1
    char = Mid$(trimmedString, x, 1)
    If char = vbTab Or char = " " Then
    Else
        trimmedString = Left$(trimmedString, x)
        Exit For
    End If
Next

You should end up with ABC{space}{space}{tab}a

Upvotes: 8

Bob77

Reputation: 13267

How about:

Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenW" ( _
    ByVal lpString As Long) As Long

Private Declare Function StrTrim Lib "shlwapi" Alias "StrTrimW" ( _
    ByVal pszSource As Long, _
    ByVal pszTrimChars As Long) As Long

Private Function TrimWS(ByVal Text As String) As String
    'Unicode-safe.
    Const WHITE_SPACE As String = " " & vbTab & vbCr & vbLf

    If StrTrim(StrPtr(Text), StrPtr(WHITE_SPACE)) Then
        TrimWS = Left$(Text, lstrlen(StrPtr(Text)))
    Else
        TrimWS = Text
    End If
End Function

It is fast, and even faster if you use typelibs instead of Declare to define the API calls.

Upvotes: 3

MatthewHagemann

Reputation: 1195

It's a shame there is no built in function. Here is the one I wrote. It does the trick.

Function TrimAllWhitespace(ByVal str As String)

    str = Trim(str)

    Do Until Not Left(str, 1) = Chr(9)
        str = Trim(Mid(str, 2, Len(str) - 1))
    Loop

    Do Until Not Right(str, 1) = Chr(9)
        str = Trim(Left(str, Len(str) - 1))
    Loop

    TrimAllWhitespace = str

End Function

Upvotes: 1

Trim all types of whitespace, including tabs

Answers (12)

Related Questions