Trimming is a function, that reduces spaces. There are at least 4 ways to Trim a string in Excel/VBA:
- Trim
- VBA.Trim
- =Trim()
- WorksheetFunction.Trim
Thus, is there a difference between these 4 actually? This is what MSDN says about String.Trim:
Returns a new string in which all leading and trailing occurrences of a set of specified characters from the current String object are removed.
However, if we take a look at Excel and the Trim() formula, it seems that it is removing the front and back spaces and reducing the multiple spaces in each cell to one cell only:
So there is one small difference between the =Trim() and the VBA.Trim() function, mentioned in the MSDN article. The VBA.Trim() should not be “touching” the inner space. Is it really so? Let’s see a small example:
|
1 2 3 4 5 6 7 8 9 10 |
Public Sub TestMe() Dim inputStringDifference As String inputStringDifference = "And well, hey, I wanna be a rockstar" Debug.Print VBA.Trim(inputStringDifference) Debug.Print Trim(inputStringDifference) Debug.Print WorksheetFunction.Trim(inputStringDifference) End Sub |
And the printed console looks like this:
Thus, the difference is that the VBA.Trim and Trim do not touch the internal spaces. WorksheetFunction.Trim and =Trim reduce the numbers of internal spaces, if they are more than one. And in general, VBA.Trim and Trim are the same. 🙂 WorksheetFunction.Trim and =Trim in the Worksheet as well. 🙂