VBA & Macro Problems

Limit the Number of Words in an Excel Cell

Nimra MalikNimra Malik Nov 14, 2026 869 views

Question details

The user wants to limit the number of words, rather than characters, entered in an Excel cell.

How to Limit the Number of Words in an Excel Cell
Product
Microsoft Excel
Device & OS
not provided
Scenario
A cell should accept no more than 99 words, including when the target cell is part of a merged area.
Observed behavior
The desired result is a cell that retains or accepts up to 99 words instead of applying a character limit.
Before you start

Identify the target cell and decide whether excess words should be removed automatically or whether Excel should reject the entry. For a merged cell, identify the top-left cell of the merged area.

Solution 1Recommended

Automatically keep the first 99 words with a worksheet macro

Use this method to trim excess words after an edit, including edits to a merged cell.

The macro counts words separated by ordinary spaces. When it trims an entry, it also removes leading, trailing, and repeated spaces.

This example monitors A1 and skips multi-cell edits other than an edit to A1's merged area. It is intended for text entries, not formula cells; formula recalculations do not trigger Worksheet_Change.

1
Open the worksheet code

In desktop Excel, open the workbook, press Alt+F11 where available, and double-click the target worksheet in the VBA Project pane.

2
Add the change event

Paste this code into the worksheet module. Replace A1 with your target cell; for a merged area, use its top-left cell. Private Sub Worksheet_Change(ByVal Target As Range) Dim watched As Range Dim cell As Range Dim text As String Dim words As Variant Set watched = Me.Range("A1") Set cell = watched.MergeArea.Cells(1, 1) If Intersect(Target, watched.MergeArea) Is Nothing Then Exit Sub If Target.CountLarge > 1 And Target.Address <> watched.MergeArea.Address Then Exit Sub If IsError(cell.Value2) Then Exit Sub text = Application.WorksheetFunction.Trim(CStr(cell.Value2)) If Len(text) = 0 Then Exit Sub words = Split(text, " ") If UBound(words) < 99 Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False ReDim Preserve words(0 To 98) cell.Value = Join(words, " ") CleanUp: Application.EnableEvents = True End Sub

3
Save and test

Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted, then enter more than 99 space-separated words in the target cell and confirm that only the first 99 remain.

Automatically keep the first 99 words with a worksheet macro
Existing worksheet event: If the worksheet module already has a Worksheet_Change procedure, integrate the logic into that procedure rather than adding a second one with the same name.
Free Microsoft Office alternative

Edit Excel Workbooks with WPS Spreadsheet

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files to move everyday spreadsheet work to WPS; it does not fix Excel's worksheet macros, and macro support can vary by version and platform.

Edit spreadsheet text and cell data in a familiar interface.Open and edit compatible Microsoft Excel .xlsx files for easier migration.Use a free, lightweight office suite for everyday spreadsheet work.
QA img-9

Frequently Asked Questions

Does Excel have a built-in word-count limit for cells?

Excel's Data Validation menu has a text-length option for characters, not a dedicated word-limit setting. Use a custom validation formula or a worksheet macro to count space-separated words.

How do I change the macro's limit from 99 to 100 words?

In the worksheet's VBA module, change If UBound(words) < 99 Then Exit Sub to If UBound(words) < 100 Then Exit Sub, and change ReDim Preserve words(0 To 98) to ReDim Preserve words(0 To 99).

How does the macro handle a merged cell?

Set the watched reference to the merged area's top-left cell. The code uses watched.MergeArea.Cells(1, 1) to read and update the value stored there.

Will the data validation rule stop someone from pasting too many words?

Not reliably. Pasting can bypass or overwrite data validation. The macro handles a direct edit or paste into the watched cell or its merged area, but the example skips other multi-cell edits.