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

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.

 

 

2. 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.

 

 

3. 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.

 

 

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 in Google Sheets

To set a character limit in Google Sheets, use a custom formula (with the LEN Function) within 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.

 

 

2. In the Data validation window, (1) choose Custom formula is under the Criteria drop down, and (2) enter the formula:

=LEN(B2)<=5

Select (3) 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.

 

 

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