Can you limit characters in an Excel cell?

See all How-To ArticlesHow to Set a Character Limit in Excel & Google SheetsThis tutorial demonstrates how to set a character limit in Excel and Google Sheets.Set a Character L

Can you limit characters in an Excel cell?

See all How-To Articles

How to Set a Character Limit in Excel & Google Sheets

This tutorial demonstrates how to set a character limit in Excel and Google Sheets.

set a character limit data validation final

Set a Character Limit

In Excel, the number of characters allowed in a single cell is 32767. However, you can set your own character limit for a text cell using Data Validation. Say you want to limit name length in cell B2 to 5 characters max.

  1. Select the cell or data range (here, cell B2) where you want to apply the data validation rule and in the Ribbon, go to Data> Data Validation.
set a character limit data validation
  1. In the Data Validation window, in the Settings tab, (1) choose Text length from the Allow drop down, then (2) select less than or equal to from the Data menu, and (3) enter a character limit for Maximum (here, 5).
set a character limit data validation 2
  1. Now in the Error Alert tab, you can customize the error message that shows when entered text exceeds the character limit. (1) Enter a title for the message (Error) and (2) the message text (Name must have up to 5 characters). Then press OK.
set a character limit data validation 3

Now a data validation rule is created for cell B2. If you enter a name that is greater than 5 characters (for example Michael), the error message will appear.

set a character limit data validation final

Set a Character Limit in Google Sheets

To set a character limit in Google Sheets, use a custom formula (with the LEN Function) in Data Validation.

  1. Select the cell (here, B2) or data range where you want to apply the data validation rule, and in the Menu, go to Data> Data validation.
google sheets data validation
  1. In the Data validation window, (1) choose Custom formula is under the Criteria drop down, and (2) enter the formula:=LEN(B2)<=5

(3) Select Reject input and (4) check Show validation help text. Now, (5) enter the error message text (Name must have up to 5 characters) and (6) click Save.

datavalidation gs len

As a result, if you enter a name longer than 5 characters (Michael), the created error message appears.

google sheets data validation 3

Video liên quan