VB and VBA Users Source Code: Performing a custom Trim() on an input string
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Performing a custom Trim() on an input string
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Tuesday, November 25, 2003
Hits:
1439
Category:
Visual Basic General
Article:
The RTrim, LTrim and Trim functions are useful from removing blank spaces. However, sometimes you want to remove different trailing/leading characters (eg, Remove all the vbNewLine from the start and end of a string). The function below allows you to specify a custom Trim: 'Purpose : Trims trailing a leading characters from a string buffer 'Inputs : sValue The string to trim ' sRemove The character/s to remove from the start/end of the sValue ' [bRtrim] If True removes any matching right hand side chars ' [bLtrim] If True removes any matching left hand side chars ' [eCompare] The method of comparison 'Outputs : Returns the string trimed from the specified characters 'Author : Andrew Baker 'Date : 17/01/2001 12:37 'Notes : Trim2("aaaaaBBBBCCCaaaa", "a") returns "BBBBCCC" 'Example : 'Assumptions : Function Trim2(ByVal sValue As String, sRemove As String, Optional bRtrim As Boolean = True, Optional bLTrim As Boolean = True, Optional eCompare As VbCompareMethod = vbBinaryCompare) As String Dim lPos As Long, lLastChar As Long, lLenRemove As Long On Error GoTo ErrFailed lLenRemove = Len(sRemove) If lLenRemove > 0 And Len(sValue) > 0 Then If bLTrim Then 'Remove the left hand chars lPos = 1 - lLenRemove lLastChar = 1 - lLenRemove 'Loop finding the chars to replace Do lPos = InStr(lPos + lLenRemove, sValue, sRemove, eCompare) If lPos = lLastChar + lLenRemove Then lLastChar = lPos Else 'Found all the matching characters Exit Do End If Loop If lLastChar Then sValue = Mid$(sValue, lLastChar + lLenRemove) End If End If If bRtrim = True And Len(sValue) > 0 Then 'Remove the right hand chars lPos = Len(sValue) + 1 lLastChar = lPos 'Loop finding the chars to replace Do lPos = InStrRev(sValue, sRemove, lPos - 1, eCompare) If lPos = lLastChar - lLenRemove Then lLastChar = lPos Else 'Found all the matching characters Exit Do End If Loop If lLastChar Then sValue = Left$(sValue, lLastChar - 1) End If End If End If Trim2 = sValue Exit Function ErrFailed: Debug.Print "Error in Trim2: " & Err.Description Debug.Assert False End Function
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
