How to use the TRIM function in Excel VBA to clean data.
Watch the video below:
This video describes the syntax and usage of the TRIM function in MS Excel. The TRIM function removes all spaces from text except for single spaces between words. We use TRIM on text data in Excel that may have irregular spacing – leading, trailing or multiple spaces. When we use search, sort and filter on such data, the results can be unexpected as shown in this example. Such leading, trailing or extra spaces cannot be easily detected with the naked eye and the results of a COUNTIF function, for example, cannot be easily understood. We can remove such unwanted spaces using TRIM, CLEAN and SUBSTITUTE functions.
The TRIM function is designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, we have an additional space character called the nonbreaking space character that has a decimal value of 160. This character is ‘ ’ and is commonly used in Web pages to create a space. TRIM function does not remove this nonbreaking space character. We can use the SUBSTITUTE function to replace the higher value Unicode characters like 160.
TRIM(text) or TRIM(Cell) or TRIM(Range)
The text argument is required and represents the text from which the spaces need to removed.
Example: =TRIM(“Excel Training Videos”) or =TRIM(A2)
Below is the complete VBA code to use TRIM in the automation mode:
Dim Rng As Range
Dim myWorkRng As Range
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))
Set myWorkRng = Sheet1.Range(“A2:A” & lastrow)
For Each Rng In myWorkRng
Rng.Value = Application.WorksheetFunction.Trim(Rng.Value)
Note: It takes a few moments for the TRIM code to execeute.