Text Macro Examples for Excel: Practical VBA Tasks

Text macro examples for Excel include cleaning extra spaces, changing text to uppercase, adding a prefix, and replacing one word with another. These small VBA procedures automate repetitive edits in selected cells; try them on a copy of your data first, because a macro changes workbook contents and its actions cannot be undone with Ctrl+Z.

What a Text Macro Does

A macro is a set of instructions that Excel runs to perform a task. In Excel for Microsoft 365 on Windows, macros are commonly written in Visual Basic for Applications (VBA). A text macro can inspect cell contents and change text according to rules, which is useful when the same cleanup or formatting operation must be repeated across many rows.

For example, a macro might standardize product codes, remove accidental spaces in imported names, or append a status label to selected entries. It can process a selected range rather than asking you to edit each cell individually. A text macro is not the same thing as a text shortcut or a text-expansion tool: here, “macro” means an Excel procedure that operates on worksheet cells.

Macros are most appropriate when the task is repetitive, the rules are clear, and you need to modify existing values. If you need a live result that recalculates when source data changes, a worksheet formula may be a better fit. Power Query is often more suitable for repeatable import and transformation steps.

How to Add and Run a VBA Macro

To use the examples, open a workbook in desktop Excel and make a backup copy. If the Developer tab is missing on Windows, choose File > Options > Customize Ribbon, check Developer, and select OK. On Mac, open Excel > Settings (called Preferences in some releases) > Ribbon & Toolbar and enable Developer. The exact labels can vary slightly by Excel release.

Select the cells the macro should process. The examples below work on the current selection.
Open the VBA editor with Alt+F11 on Windows. On Mac, use Option+F11; depending on the keyboard, you may also need the Fn key, or open Visual Basic from the Developer tab.
In the editor, choose Insert > Module, then paste the procedure into the module.
Return to Excel and run it from Developer > Macros, select its name, and choose Run. In the VBA editor, F5 can run a procedure when the cursor is inside it.

To keep macros in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file does not store VBA code. Macro-enabled files can contain active code, so only run code from a source you trust; do not lower security settings or enable all macros just to run an unfamiliar file.

Example: Remove Extra Spaces

Imported data often contains leading or trailing spaces, repeated spaces between words, or nonbreaking spaces copied from a web page. The following macro cleans selected cells that contain text. It skips formulas, numbers, and error values, so it does not replace formula results with fixed text.

Sub CleanSelectedText()
    Dim cell As Range

    ' Process text constants in the current selection only.
    For Each cell In Selection.Cells
        If Not cell.HasFormula Then
            If VarType(cell.Value2) = vbString Then
                cell.Value2 = Application.WorksheetFunction.Trim( _
                    Replace(CStr(cell.Value2), ChrW(160), " "))
            End If
        End If
    Next cell
End Sub

Select the target cells before running it. Excel’s TRIM worksheet function removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. The Replace call changes nonbreaking spaces (character 160) to ordinary spaces first, allowing TRIM to handle them too. This can improve text copied from websites, but it also collapses intentional repeated spaces, so do not use it on text where spacing itself carries meaning.

Example: Change Selected Text to Uppercase

Uppercase text can make codes or labels consistent. This example changes text constants in the selected cells to uppercase, while leaving formulas and non-text values alone. It changes the cell values themselves rather than merely changing how they look.

Sub UppercaseSelectedText()
    Dim cell As Range

    ' Change text constants, not formula cells or numbers.
    For Each cell In Selection.Cells
        If Not cell.HasFormula Then
            If VarType(cell.Value2) = vbString Then
                cell.Value2 = UCase(CStr(cell.Value2))
            End If
        End If
    Next cell
End Sub

For instance, a selected text value such as ab-104 becomes AB-104. Uppercase conversion is not always suitable for names, addresses, or prose, and it may not follow every language’s capitalization conventions. Use it for fields where a consistent uppercase format is actually required, such as certain identifiers.

Example: Add a Prefix or Label

A prefix macro can add a fixed marker to selected text entries, such as a project code or review label. This version adds SKU- to nonblank text constants only. It skips formulas, numbers, errors, and empty text cells.

Sub AddSkuPrefix()
    Dim cell As Range

    ' Add the prefix to nonblank text constants in the selection.
    For Each cell In Selection.Cells
        If Not cell.HasFormula Then
            If VarType(cell.Value2) = vbString Then
                If Len(CStr(cell.Value2)) > 0 Then
                    cell.Value2 = "SKU-" & CStr(cell.Value2)
                End If
            End If
        End If
    Next cell
End Sub

Replace SKU- with the label your workflow requires before running the procedure. Be cautious about running an append-style macro twice: it will add the prefix twice because the example does not check whether the text already starts with that label. If repeat runs are likely, add a check for the existing prefix or work on a fresh copy of the original data.

Example: Replace a Word or Spelling Variant

VBA’s built-in Replace function can change a specified piece of text inside selected text constants. This example changes “colour” to “color” without distinguishing uppercase and lowercase matches. It skips formulas and non-text cells.

Sub ReplaceColourWithColor()
    Dim cell As Range

    ' Replace matching text in selected text constants.
    For Each cell In Selection.Cells
        If Not cell.HasFormula Then
            If VarType(cell.Value2) = vbString Then
                cell.Value2 = Replace(CStr(cell.Value2), _
                    "colour", "color", 1, -1, vbTextCompare)
            End If
        End If
    Next cell
End Sub

This replaces matching character sequences wherever they occur in a cell, not only a complete word. It can therefore change a longer word that contains the same sequence. The case-insensitive comparison also writes the replacement as “color,” regardless of the original capitalization. If exact whole-word replacement or preservation of capitalization matters, use a more carefully designed procedure and test it on representative examples first.

Choosing Between a Macro, a Formula, and Find and Replace

A macro is not automatically the best way to handle text. Choose the simplest tool that meets the need:

Use a formula when you want a result in a separate column that updates from its source. Functions such as TRIM and UPPER can transform text without overwriting the original cells.
Use Find and Replace for a straightforward, one-time replacement. In Excel, press Ctrl+H on Windows to open Find and Replace; on Mac, use Control+H in many configurations or open Find from the Home tab if the shortcut differs.
Use a macro when a repeatable operation involves many cells or several steps, and you want to run the same procedure again with a selection.
Use Power Query when cleaning recurring data imports. It records transformation steps that can be refreshed, rather than editing the source cells through a one-off macro.

One important distinction is that the sample macros overwrite selected text values. A formula usually leaves the source value intact and displays its result elsewhere. If preserving the original is important, duplicate the data first or adapt the process to write results to a new column.

Safety, Limits, and Good Habits

VBA macros can make broad changes quickly, so a small selection mistake can affect more cells than intended. Save a separate copy before running a macro, select only the intended range, and test with a few sample cells. Excel does not provide a Ctrl+Z undo history for VBA macro actions; closing without saving may recover the prior workbook state, but it is safer to keep a deliberate backup.

The examples deliberately skip formula cells to avoid replacing formulas with their current displayed results. They also operate on the selection, so running one with an entire column selected may inspect a very large number of cells. For large worksheets, consider limiting the selection to the populated range and adding checks tailored to the data. These examples do not validate whether the resulting text is a valid identifier, email address, or other business-specific value.

VBA is available in desktop Excel for Windows and Mac, but macros do not run in Excel for the web. Excel for the web can open and edit many macro-enabled workbooks, but you need desktop Excel to create or run VBA macros. Macros may also be blocked by organizational security policies; contact your administrator rather than attempting to bypass those controls.

Common Problems and Fixes

The macro does not appear in the list: Confirm that the code is in a standard module, that it begins with a public Sub procedure such as the examples, and that the workbook is saved as .xlsm if you want to retain the code.
Excel warns that macros are disabled: The file may have come from an untrusted source or be subject to your organization’s policy. Verify the source and follow approved security instructions; do not enable unknown code.
The macro changes nothing: Check that the intended cells were selected and contain text constants. The examples intentionally skip formulas, numbers, and blank cells.
Spacing still looks unusual: The cleanup example handles ordinary and nonbreaking spaces, but other invisible characters, line breaks, or tabs may remain. Identify the character in the source data before adding further cleanup rules.

For a useful test, place a few examples in a spare worksheet: a normal text value, one with extra spaces, a formula, a number, and an empty cell. Run the macro only on that small test range and inspect each result. This reveals whether the procedure matches the actual data before you apply it to a larger workbook.

Frequently Asked Questions

What is a text macro in Excel?
A text macro is a VBA procedure that automates an operation on text in worksheet cells, such as cleaning spaces, changing capitalization, or replacing a phrase.

Can I undo an Excel macro with Ctrl+Z?
No. Excel’s normal undo history does not reverse VBA macro actions. Save a copy before running a macro, or test it on duplicate data.

Can I run VBA text macros in Excel for the web?
No. Excel for the web can open many macro-enabled workbooks, but VBA macros must be created and run in desktop Excel for Windows or Mac.

Will these macros change formulas?
The examples check for formulas and skip those cells. They change eligible text constants in the selected range, not formula results.

Do I need to save the workbook as XLSM?
Use the .xlsm format if the workbook needs to retain its VBA macro. Saving as .xlsx removes the VBA project from the file.

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.