Limit the Number of Words in an Excel Cell
Question details
The user wants to limit the number of words, rather than characters, entered 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.
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.
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.
In desktop Excel, open the workbook, press Alt+F11 where available, and double-click the target worksheet in the VBA Project pane.
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
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.

Reject entries over 99 words with data validation
Use this option for an unmerged cell when you prefer an error message instead of automatically deleting extra words.
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.

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.




