Excel VBA Trim Text: What It Removes and How to Use It

VBA & macros · Updated November 18, 2026

In Excel VBA, use Trim$(textValue) to remove ordinary spaces at the beginning and end of a string; it leaves spaces between words alone. To clean a worksheet range, loop through its text cells and assign the trimmed strings back, taking care to preserve formulas and save a copy first.

What VBA Trim Removes

VBA’s Trim function removes leading and trailing spaces. For example, Trim$(" Cedar Grove ") returns "Cedar Grove". The two spaces between the words remain: trimming is not the same as collapsing repeated spaces throughout a string.

VBA also provides LTrim$ and RTrim$ when you only want to clean one end:

  • LTrim$(textValue) removes spaces from the left, or start.
  • RTrim$(textValue) removes spaces from the right, or end.
  • Trim$(textValue) removes spaces from both ends.

The dollar-sign versions return a String. They are useful when you are deliberately working with text and want to make that intent clear. Without the dollar sign, Trim returns a Variant containing a string. In ordinary text-cleaning code, either form can work, but Trim$ is a common choice.

“Space” here means the ordinary space character commonly entered with the space bar. Tabs and nonbreaking spaces are different characters, so they may remain even after applying Trim$. That distinction explains many cases where a value appears to have been trimmed but still does not match another value.

Use Trim in a VBA Procedure

To trim a variable, pass it to the function and store the result. The example below adds brackets to the output so that the removed spaces are easy to see in the Immediate window (open it in the VBA editor with Ctrl+G on Windows).

Sub ShowTrimExample()
    Dim textValue As String
    Dim cleanedText As String

    ' Remove ordinary spaces from both ends.
    textValue = "  Cedar Grove  "
    cleanedText = Trim$(textValue)

    Debug.Print "[" & cleanedText & "]"  ' Prints [Cedar Grove]
End Sub

For a cell, read its value into a variable, trim it, and write the result where you intend. For example, with text in A2, this statement reads the cell, removes its outside spaces, and stores the result in B2: Range("B2").Value = Trim$(CStr(Range("A2").Value2)). The explicit conversion with CStr makes the string operation clear, although code should account for error values before converting them.

Using Value2 reads the cell’s underlying value without the date and currency conversions associated with the Value property. For a text-cleaning task, that distinction usually matters less than checking whether the cell contains an error or a formula before writing anything back.

Trim Text in a Selected Worksheet Range

This macro trims text constants in the selected cells in place. It skips formulas, error values, numbers, and dates, so it will not convert numeric data to text or replace formula results with fixed values. Select only the intended range before running it.

Sub TrimTextInSelection()
    Dim target As Range
    Dim cell As Range
    Dim cellValue As Variant

    ' Select a worksheet range before running this macro.
    Set target = Selection

    For Each cell In target.Cells
        If Not cell.HasFormula Then
            cellValue = cell.Value2
            If Not IsError(cellValue) Then
                If VarType(cellValue) = vbString Then
                    cell.Value2 = Trim$(CStr(cellValue))
                End If
            End If
        End If
    Next cell
End Sub

Before running a macro that changes cells, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, and a mistaken selection can affect many cells at once. This procedure changes selected text values directly, so the backup is especially important if the source values need to be retained.

If you want to keep the original data intact, write cleaned values to a new column instead. For example, select the source range, copy it to a destination column, then run the macro on the copied text. Another option is to write a procedure that reads each source cell and assigns the result to a corresponding destination cell. That approach is safer for auditing and lets you compare original and cleaned values before replacing anything.

VBA Trim Versus Excel’s TRIM Function

Excel has a worksheet function named TRIM, and it behaves differently from VBA’s Trim$. The worksheet function removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. VBA’s Trim$ removes spaces only from the ends and preserves internal runs.

For example, given the text " Cedar Grove ", VBA Trim$ returns "Cedar Grove", preserving the three internal spaces. Excel’s worksheet TRIM returns "Cedar Grove", with one internal space. If you need worksheet-style behavior inside a VBA procedure, call the worksheet function:

Sub ShowWorksheetTrim()
    Dim textValue As String
    Dim cleanedText As String

    ' WorksheetFunction.Trim also reduces repeated internal spaces.
    textValue = "  Cedar   Grove  "
    cleanedText = Application.WorksheetFunction.Trim(textValue)

    Debug.Print cleanedText  ' Prints Cedar Grove
End Sub

Choose based on what the text represents. For a label where multiple spaces may be intentional, VBA Trim$ is less destructive. For imported names or descriptions where repeated spaces are unwanted, worksheet TRIM may be a better fit. Test on representative data before applying either method to a whole column: a run of spaces might be meaningful in a formatted code or fixed-width text field.

Tabs, Nonbreaking Spaces, and Other Hidden Characters

A common misconception is that “trim” removes every invisible character. It does not. A tab, line break, or nonbreaking space can look like blank space but is not the ordinary space character handled by these trim functions. Such characters often arrive in copied web text, exported reports, or data from another system.

For a tab that should become a normal space, VBA’s Replace function can replace vbTab before trimming. For nonbreaking spaces, the character is commonly represented by code 160; in VBA, ChrW(160) can identify it. A simple sequence is to replace the unwanted character with a regular space and then call Trim$. If the source may contain several kinds of control characters, decide explicitly which to replace rather than deleting all characters indiscriminately.

Excel’s worksheet TRIM also does not reliably remove nonbreaking spaces, and CLEAN is not a universal Unicode cleanup function. CLEAN removes certain nonprinting characters, but it will not solve every issue involving spaces copied from webpages or other applications. A useful troubleshooting step is to inspect the character code in a short test, or replace known problem characters before trimming.

Be careful when replacing a nonbreaking space with nothing: if it separates two words, the words will join. Replacing it with an ordinary space first, then trimming, usually preserves the intended word boundary. Conversely, if the value is a fixed identifier, replacing characters may alter the identifier, so check the data rules before cleaning it.

Choosing a Safe Cleanup Method

The right approach depends on where the text is and whether the internal spacing should change. Use this quick guide:

  • For a single VBA string, use Trim$ for the leading and trailing spaces.
  • For only the left or right edge, use LTrim$ or RTrim$.
  • For a worksheet formula that also collapses repeated spaces between words, use Excel’s TRIM function in a cell.
  • For a VBA routine that needs worksheet-style collapsing, use Application.WorksheetFunction.Trim.
  • For tabs or nonbreaking spaces, replace those specific characters deliberately, then trim.

For a one-time cleanup, a worksheet formula in a helper column is often easier to review than a macro: the original remains visible, and you can inspect results before pasting values over the source. A macro is useful when the same process needs to be repeated or applied to many ranges. If you use a macro, document what it changes, target only the intended cells, and keep a backup.

Remember that a cell can look blank but contain spaces. Those values can affect sorting, matching, and duplicate checks. Trimming text may resolve mismatches caused by ordinary outside spaces, but if two apparently identical values still fail to match, investigate nonbreaking spaces, tabs, line breaks, punctuation, and differences in the actual text rather than repeatedly applying Trim$.

Macro Use, Compatibility, and Undo

VBA macros run in the desktop versions of Excel, including Microsoft 365 for Windows and Mac. Excel for the web can open a macro-enabled workbook, but it cannot create or run VBA macros. If you need the macro to remain in the workbook, save it in a macro-enabled format such as .xlsm; saving as a standard .xlsx file does not preserve VBA code.

On Windows, open the Visual Basic Editor with Alt+F11; on Mac, the equivalent is generally Option+F11, though some keyboards require Fn as well. You can also use the Developer tab’s Visual Basic command. If the Developer tab is not shown, it can be enabled in Excel’s ribbon settings. Macro security settings may block macros from running, particularly for files from the internet; only enable macros when you trust the workbook and its source.

Macros do not have a normal worksheet undo history. After a procedure changes cells, Ctrl+Z on Windows or Command+Z on Mac will not restore the prior values. Close without saving if appropriate, or restore from the backup. This is why a helper column, a test copy, and a carefully limited selection are practical safeguards rather than unnecessary steps.

Frequently Asked Questions

What does Trim do in VBA?
It removes ordinary spaces from the beginning and end of a string. It does not remove spaces between words or automatically clean every kind of invisible character.

Does VBA Trim remove spaces between words?
No. VBA Trim$ preserves internal spaces, including repeated ones. Use Excel’s worksheet TRIM, or Application.WorksheetFunction.Trim in VBA, if repeated internal spaces should become single spaces.

How do I remove tabs or nonbreaking spaces?
Replace those specific characters before trimming. In VBA, vbTab identifies a tab, and ChrW(160) identifies the commonly encountered nonbreaking space. Replace carefully so you do not join words or change meaningful data.

Can I use Trim directly on a worksheet range?
Not as a direct range-wide VBA operation. Loop through the cells, or use a worksheet formula in a helper column. When using a macro, skip formulas and error values if you do not intend to overwrite them.

Can I undo a trim macro with Ctrl+Z?
No. VBA macro changes cannot be undone through Excel’s regular undo command. Save a separate copy before running a macro, especially one that edits cells in place.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.