If the text is in A2, enter =TRIM(A2) in another cell to remove leading and trailing ordinary spaces and reduce repeated spaces between words to one. For a one-click cleanup, select the cells and run the VBA macro below; it changes selected text cells in place, so save a copy of the workbook first.
What a trim macro does in Excel
“Trim Excel macro” can mean either a VBA macro that cleans cells or Excel’s built-in TRIM worksheet function. The function calculates a cleaned result in a separate cell; a macro is a small program that can replace the contents of selected cells directly. Excel does not provide a standard Trim button that runs this exact cleanup as a macro, but you can add and run your own VBA code.
Cleaning spaces is useful when data has been copied from websites, exported from another system, or entered inconsistently. Extra spaces can make two names look identical while causing an exact-match lookup to treat them as different text. Trimming can also make sorting, comparisons, and duplicate checks more dependable. It does not, however, correct misspellings, inconsistent punctuation, or every kind of invisible character.
How Excel TRIM handles spaces
Assume A2 contains the text Maya Chen , with two spaces before and after the name and three between the words. Enter =TRIM(A2) in B2. The result is Maya Chen: one ordinary space remains between the words, while the extra leading, trailing, and repeated spaces are removed.
Excel’s TRIM function is designed around the ordinary space character, ASCII 32. It does not remove every character that may look like a space. A common exception is the nonbreaking space, often found in copied web content. If A2 contains that character, a formula that replaces it with an ordinary space before trimming can help. With the problem text in A2, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). For example, if the words are separated by a nonbreaking space and there are extra ordinary spaces around them, the result is a single ordinary space between the words.
That formula addresses CHAR(160), not every possible Unicode spacing character. If the cleaned result still looks unchanged, the unwanted character may be a different control or Unicode character. Identify the source before applying a broad replacement: replacing characters indiscriminately can damage legitimate text or codes.
VBA macro for trimming selected cells
This macro applies Excel’s TRIM behavior to selected cells that contain text constants. It leaves formulas, numbers, dates stored as numbers, and error values alone. That makes it safer than a routine that rewrites every selected cell, but it still changes text in place.
- Save a backup copy of the workbook. VBA changes cannot be reversed with Ctrl+Z.
- In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module.
- Paste the code below into the module, then close or switch away from the editor.
- Select the cells you want to clean in the worksheet. Press Alt+F8, choose
TrimSelectedTextCells, and select Run.
Option Explicit
Public Sub TrimSelectedTextCells()
Dim target As Range
Dim cell As Range
' Require a worksheet cell selection.
If TypeName(Selection) <> "Range" Then
MsgBox "Select worksheet cells before running this macro."
Exit Sub
End If
Set target = Selection
' Clean text constants; leave formulas, errors, and other values alone.
For Each cell In target.Cells
If Not cell.HasFormula Then
If Not IsError(cell.Value2) Then
If VarType(cell.Value2) = vbString Then
cell.Value2 = Application.WorksheetFunction.Trim(CStr(cell.Value2))
End If
End If
End If
Next cell
End Sub
The code uses the Excel worksheet TRIM implementation through VBA. It does not remove nonbreaking spaces, so data with CHAR(160) may need a separate cleanup step. The macro also does not report how many cells changed. If you need a record of the original values, work on a duplicate sheet or copy the selected data elsewhere before running it.
What the macro changes—and what it leaves alone
Only text constants in the selection are assigned a new value. A cell containing North Office becomes North Office. A formula such as =A1&B1 is skipped, even if its displayed result has extra spaces; edit or wrap the formula instead if its output needs cleaning. Numeric values and dates are also skipped because Excel stores them as numbers, not as text strings.
- Text values: leading and trailing ordinary spaces are removed, and repeated ordinary spaces inside the text become one.
- Formulas: left unchanged so the macro does not replace a calculation with its displayed result.
- Numbers, dates, and errors: left unchanged.
- Nonbreaking spaces: not removed by this version of the macro.
Review the selected range before running the macro. If the selection includes a heading, an identifier where spaces are meaningful, or cells on which other work depends, those text values will also be modified. Avoid merged cells in the selection; merged-cell layouts can behave unexpectedly when a macro visits each cell individually.
Save, enable, and run macros safely
A workbook containing VBA must be saved in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm). If you save it as the standard .xlsx format, Excel cannot retain the VBA project in that file. Keep the original workbook or a separate backup before converting or running code.
If the Developer tab is hidden in Windows Excel, open File > Options > Customize Ribbon and select Developer. You can then use Developer > Macros to run the procedure. The Alt+F8 shortcut opens the macro list in Windows. Macro security settings may block code from running; enable macros only for workbooks and code you trust, and follow your organization’s security rules.
On Excel for Mac, VBA is available in the desktop application, though keyboard shortcuts can depend on the keyboard and function-key settings. Option+F11 opens the Visual Basic Editor on many Mac keyboards; you may need to hold Fn as well. The Developer tab can be enabled in Excel > Settings > Ribbon & Toolbar. Use the macro list or Developer tab to run the procedure if the shortcut differs on your setup. Excel for the web does not create or run VBA macros; open the file in desktop Excel to use this code.
Formula, macro, or another cleanup method?
Choose a formula when you want to preserve the original data and inspect the cleaned result before replacing anything. For example, with source text in A2:A20, enter =TRIM(A2) in B2 and fill the formula down. Check the results, then copy the cleaned range and use Paste Special > Values if you decide to replace the source. This method is transparent and easy to review, but it requires a destination range and an extra replacement step.
Choose the macro when you repeatedly need to clean selected text cells in place and are comfortable enabling VBA. It is convenient for repeated manual tasks, but it alters data and is unavailable in Excel for the web. A formula is usually the better choice when you want a reversible, visible transformation or need to keep an untouched source column.
Power Query is another option for recurring imported data-cleaning workflows. In Excel for Microsoft 365 on Windows, use Data > From Table/Range to open the Power Query Editor, select the relevant text column, then choose Transform > Format > Trim. Load the result back to Excel when ready. Power Query transformations are applied again when you refresh the query, which is useful for repeated imports; this is different from a VBA macro that edits the current worksheet cells directly.
Common problems and misconceptions
A frequent misconception is that TRIM removes all invisible characters. It does not. A cell can appear to contain ordinary spaces but actually contain a nonbreaking space or another character. If a cleaned value still fails an exact comparison, inspect the source or test for the specific character rather than repeatedly applying TRIM.
Another issue is expecting a macro to clean formula results. This macro intentionally skips formulas to preserve them. If a formula result needs trimming, change the formula itself—for example, if the original expression is =A1&B1, a wrapper can be used: with that expression as the text-producing calculation, enter =TRIM(A1&B1). Check that trimming is appropriate for the data, since spaces may be meaningful in some identifiers or formatted strings.
Finally, trimming does not remove all duplicate records or standardize names. J. Smith and J Smith remain different, as do Lee and LEE in the displayed text. Decide on additional normalization rules separately, and verify the results against the purpose of the dataset.
Frequently Asked Questions
Does Excel have a built-in Trim macro?
Excel has a built-in TRIM worksheet function, but it does not include a standard one-click Trim macro for cleaning selected cells in place. You can use the function in a formula or add the VBA procedure above.
Can I trim a whole column with the macro?
Yes. Select the cells you want to process in the column and run the macro. Selecting an entire worksheet column may be slow because Excel has many rows, so selecting only the used range is generally more practical.
Will the macro remove spaces inside names?
It reduces multiple ordinary spaces between words to one; it does not join the words. For example, Mary Ann becomes Mary Ann, not MaryAnn.
Why did TRIM not remove the space I can see?
The character may be a nonbreaking space or another Unicode spacing character rather than the ordinary space that TRIM handles. A formula using SUBSTITUTE with CHAR(160) can address the common nonbreaking-space case, but other characters need their own treatment.
Can I undo a trim macro with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot restore the cells changed by the macro. Save a copy or duplicate the source range before running it.
Does this VBA macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, or use a formula-based cleanup in the web version.
Related
- Excel Arrow Keys Not Moving Between Cells? Quick Fixes
- Excel Not Responding? Causes, Fixes, and Recovery Steps
- Excel Slow? Causes, Fixes, and Ways to Speed It Up
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Word VBA: How to Add and Format Headers in a DocumentTo add a header with Word VBA, set the header range’s text, for example ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "Quarterly Report".…
- Macro Recorder for Windows: Options and How to ChooseA macro recorder for Windows captures repeated actions and replays them, but Windows does not provide one universal…
- VBA MsgBox Auto Close: Options and LimitationsA standard VBA MsgBox cannot be set to close itself after a number of seconds: its arguments include…
- VBA Loop Example: For, Do While, and For Each LoopsA VBA loop repeats a block of code, either a set number of times or until a condition…
- Excel VBA AutoFit: Resize Rows and Columns AutomaticallyIn Excel VBA, use the AutoFit method on the rows or columns you want to size. For example,…
- MS Access VBA Dictionary: Setup, Examples, and TipsYes. Microsoft Access VBA can use the Scripting.Dictionary object to store key-value pairs, look up a value by…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.