In VBA, Trim is a standard built-in string function that removes ordinary spaces from the beginning and end of a string. For example, Trim$(" Ada ") returns "Ada". It does not remove spaces between words, and it does not reliably clean other whitespace characters such as tabs or nonbreaking spaces.
What Trim Means in VBA
Trim is the name of a standard VBA function, not slang, an informal term, or an acronym. It takes a string expression and returns a copy with leading and trailing spaces removed. In ordinary English, “trim” can mean to cut away something extra; in code, the function applies that idea specifically to spaces at the two ends of text.
VBA also provides Trim$. It performs the same trimming operation, but its declared return type is String; Trim returns a Variant containing a string. When the input is known to be text, programmers often use Trim$ to make the intended type explicit. You will also see Trim in older code, examples, and documentation, and it remains valid VBA.
A normal sentence using the term would be: “Use Trim$ to remove accidental spaces from the ends of the imported name.” The name follows the ordinary English verb rather than standing for a technical phrase.
Origin and VBA Context
The English word trim has long been used as a verb meaning to make something neat or remove excess material. The programming function uses that familiar meaning for text. In VBA, Trim belongs to the language’s built-in string-function set, alongside Left, Right, Mid, LTrim, and RTrim. It does not need a worksheet formula or an added reference to be available in an ordinary VBA project.
VBA code is often used to process values in Excel cells, imported text files, form fields, and other Office data. A value that looks like “North” may actually contain spaces before or after the visible letters. That can cause exact comparisons, lookups, or data matching to fail. Trimming at an appropriate point helps normalize that input. It is not a general-purpose data-cleaning operation, however: it does not validate the text, remove punctuation, change capitalization, or standardize every kind of whitespace.
Exactly What Trim Removes
VBA’s Trim removes ordinary space characters from the left and right edges of its input. It leaves the characters between the first and last non-space characters alone. If the string is made up only of ordinary spaces, the result is an empty string. If the input has no ordinary spaces at either end, the returned text is unchanged.
Trim$(" Maple Street ")returns"Maple Street".Trim$("Maple Street")returns"Maple Street"; the two internal spaces remain.Trim$(" ")returns an empty string.Trim$("")returns an empty string.
That second example is a useful boundary to remember. Trimming is not the same as collapsing repeated spaces. It removes space characters only while they are at the edges. It also does not remove leading or trailing tabs, line breaks, or every Unicode character that may look like a space. Those cases need deliberate handling, especially when text comes from web pages or external systems.
How to Use Trim in a VBA Procedure
Use Trim$ around a text expression and assign the returned value to a string variable, or use it directly in a comparison. The following small procedure prints the result in the Immediate window; it does not edit worksheet cells.
Sub ShowTrimExample()
Dim rawText As String
Dim cleanedText As String
' Save a copy of the workbook before running any macro.
' Macros cannot be undone with Ctrl+Z.
rawText = " North Wing "
cleanedText = Trim$(rawText)
Debug.Print "[" & cleanedText & "]"
End Sub
The output is [North Wing]. The brackets make the edges visible: the outer spaces are gone, but the pair of spaces between “North” and “Wing” remains. In the VBA editor on Windows, the Immediate window can be shown with Ctrl+G. On Mac, use the Visual Basic Editor’s View menu to show the Immediate window; available editor shortcuts can differ by keyboard and Office version.
For a comparison, trim both values if either may contain accidental edge spaces. With firstName and secondName declared as String, for example, the condition Trim$(firstName) = Trim$(secondName) ignores ordinary spaces at the edges. It remains case-sensitive under a binary comparison setting, so trimming alone does not make “Ada” and “ada” equal.
Trim, LTrim, RTrim, and Excel’s TRIM
VBA includes three related functions. LTrim removes leading spaces only, RTrim removes trailing spaces only, and Trim removes spaces at both ends. Their dollar-sign forms, such as LTrim$ and RTrim$, return strings. Choose the one that matches the task rather than combining several functions unnecessarily.
Excel’s worksheet function TRIM is related but has an important difference: it removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. VBA’s built-in Trim does not reduce internal spaces. Therefore, code that uses Trim$(text) should not be assumed to behave exactly like an Excel worksheet cell using TRIM. This distinction often explains why a cleaned cell formula and a VBA result appear different.
If you specifically need Excel worksheet-style handling inside VBA, Excel exposes its worksheet function through Application.WorksheetFunction.Trim. That choice can be appropriate when matching worksheet behavior, but it is not a substitute for understanding the input: neither version should be treated as a universal cleaner for tabs, line breaks, or nonbreaking spaces.
Handling Tabs and Nonbreaking Spaces
A tab or a nonbreaking space can look like a normal space on screen while remaining a different character. VBA’s built-in Trim is defined for ordinary spaces, so it will not necessarily remove those characters at the edges. This is common with copied web text, pasted reports, and data exported from other systems. If a comparison still fails after trimming, inspect and normalize the specific characters present rather than repeatedly applying Trim.
For example, replace tabs with ordinary spaces first, then trim the edges:
Sub ReplaceTabsThenTrim()
Dim rawText As String
Dim cleanedText As String
' Save a copy of the workbook before running any macro.
' Macros cannot be undone with Ctrl+Z.
rawText = vbTab & " Cedar Road " & vbTab
cleanedText = Trim$(Replace(rawText, vbTab, " "))
Debug.Print "[" & cleanedText & "]"
End Sub
Here, Replace changes each tab to an ordinary space, which Trim$ can then remove at the ends. For a nonbreaking space, VBA can represent the character with ChrW(160); replacing that character with an ordinary space before trimming is a common targeted approach. Avoid replacing every unfamiliar character blindly: some characters may be meaningful in the source data, and a cleanup rule should reflect the data’s actual format.
Using Trim with Worksheet Data
A common reason to use Trim in VBA is to prepare text read from a cell before comparing or storing it. If a macro assigns a cell’s text to a string variable, it can trim that variable before using it. Take care when writing cleaned values back: a range may contain formulas, numbers, dates, or error values, not just plain text. Converting every cell to a string or overwriting a mixed-data range can change information that should have been preserved.
For a simple name field known to contain text, a safe workflow is to read the value, check that it is appropriate to process, and then trim the text. For bulk cleanup, work on a copy of the workbook or a separate output range first. A macro that changes cell contents cannot be reversed with Excel’s Undo command. This applies on Windows and Mac; Excel for the web does not run VBA macros, so run VBA from the desktop Excel application.
Trimming also does not remove empty rows, remove duplicates, standardize spelling, or make text case-insensitive. Treat it as one small normalization step within a wider process, and check the results before replacing source data.
Common Problems and How to Choose
- Internal spaces remain: That is expected for VBA
Trim. Use a separate rule if repeated spaces inside the text must be changed. - A value still looks padded: It may contain tabs, nonbreaking spaces, or another character that resembles a space. Identify the character and replace it deliberately.
- A comparison still differs: Trimming handles edge spaces, not letter case, punctuation, or spelling. Normalize those separately if required.
- A value is not text: Check its type before converting it. A cell can contain a date, number, error, or empty value, and conversion may not preserve its original meaning.
- Choosing
TrimorTrim$: Both trim the same way. PreferTrim$when your code expects a string; useTrimif you specifically need the Variant-returning form.
In short, use VBA Trim$ for the ordinary, narrow task of removing normal spaces at the beginning and end of known text. Use a more specific transformation when the requirement involves internal spacing, unusual whitespace, or non-text cell values.
Frequently Asked Questions
Does VBA Trim remove spaces between words?
No. It removes ordinary spaces at the beginning and end only. For example, trimming "Oak Lane" leaves the two internal spaces unchanged.
What is the difference between Trim and Trim$ in VBA?
They perform the same edge-space removal. Trim returns a Variant containing a string, while Trim$ returns a String. The dollar sign is a VBA type-declaration suffix, not part of a different cleaning rule.
Does VBA Trim remove tabs?
No. A tab is not an ordinary space. Replace tabs with ordinary spaces or remove them explicitly before applying Trim, depending on the intended result.
Is VBA Trim the same as Excel’s TRIM function?
No. Excel’s worksheet TRIM also reduces repeated internal ordinary spaces to one. VBA’s built-in Trim leaves internal spacing unchanged.
Why does Trim not remove a space copied from a website?
The character may be a nonbreaking space rather than an ordinary space. Identify it and replace it with a normal space or remove it explicitly before trimming.
Related
- Compress Pictures in PowerPoint: Steps and Best Settings
- Can You Record a Presentation in PowerPoint and Export It
- Visual Budget Spreadsheet Template: Build a Monthly Plan
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Reference Is Not Valid: Causes and Fixes“The sort reference is not valid” is an Excel sorting error, not a VBA syntax rule. In a…
- VBA MsgBox: Create an OK or Decline Box?Yes. In VBA, use MsgBox with the vbOKCancel button style to show OK and Cancel, or vbYesNo to…
- Option Button in Excel VBA: Add, Group, and Use ItYes. In Excel for Microsoft 365 on Windows, add an option button from Developer > Insert, then choose…
- Google Sheets Macros: How They Work and How to Use ThemYes. Google Sheets supports macros: you can record a sequence of spreadsheet actions and replay it, or customize…
- Delete an Excel Macro: Safe Steps for Windows and MacTo delete an Excel macro in Microsoft 365 for Windows, open the workbook, choose Developer > Macros, select…
- Excel VBA Runtime Error 70: Causes and FixesExcel VBA runtime error 70, “Permission denied,” means that a VBA operation was refused access to a file,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.