How to limit characters length in an Excel cell?
A cell in Excel can hold a maximum of 32,767 characters, with only 1,024 characters displayed and all 32,767 visible in the Formula bar. However, you might sometimes need to restrict user input to a certain number of characters in a cell, such as limiting it to 10 characters. This tutorial will show you how to effectively limit the number of characters in an Excel cell.
- Limit the number of characters in a cell
- Set Input Message for informing character limit
- Activate Error Alert for character limit violations
- Video: How to limit characters length in an Excel cell?
Limit the number of characters in a cell
This section covers the steps to restrict the number of characters that can be entered into a specific cell or range of cells.
- Select the cell range where you want to enforce the character length limit.
- Click Data Validation in the Data Tools group under the Data tab.
- In the Data Validation dialog box, please set the validation rule as follows:
- From the Allow: drop-down menu, select Text Length.
- From the Data: dropdown, select the criteria based on your requirements:
- Choose the equal to option for an exact character count, e.g., 10 characters.
- Select less than or equal to for a maximum character limit, e.g., up to 10 characters.
- Opt for greater than for more than a specific count, e.g., more than 10 characters.
- In the Maximum/Minimum/Length: box, input the desired number for the character limit according to your needs.
- Click OK.
Now, users will be restricted to input text within the set character limits in the selected cells.
Easily prevent from typing special characters, numbers, or letters in a cell/selection in Excel
B. Restrict typing of specific characters, such as numbers or certain letters;
C. Only allow typing in specified characters, such as numbers or letters, as you need.
Set Input Message for informing character limit
Here, we'll explain how to set up input messages that inform users about the character limitations before they begin typing as below screenshot shown:
- Select the cells where you will add an input message.
- Click Data Validation in the Data Tools group under the Data tab.
- In the Data Validation dialog box, select the Input Message tab, and do as follows:
- Check the Show input message when cell is selected option.
- Enter the message title and message content.
- Click OK.
Now, when you click on a cell where the input message is configured, a tooltip will appear, displaying the message title and content you entered earlier.
Activate Error Alert for character limit violations
This section will guide you through configuring error alerts in Excel, designed to immediately notify users when the entered data does not meet the specified character limit. See screenshot:
- Select the cells where you want to activate an error alert for instances of invalid data entry.
- Click Data Validation in the Data Tools group under the Data tab.
- In the Data Validation dialog box, select the Error Alert tab, and do as follows:
- Check the Show error alert after invalid data in entered option.
- Select the Warning option from the Style: drop-down box.
- Enter the alert title and error message.
- Click OK.
From now on, if the text entered in a cell is invalid, such as exceeding 10 characters, a warning dialog box will appear, displaying the preset alert title and message.
Demo: Limit character length in cells with input message & alert warning
One click to prevent from entering duplicate data in a single column/list
Related Article:
How to limit cell value entries in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!