Limit # of characters in a cell

Excel Tip #017

Limit # of characters in a cell

We would have come across situations where we want to restrict the end user from typing lengthy sentence in a cell which would then get itself autofit (or) sometimes we don't see the entire comment if the adjacent cell has data in it.

To overcome this situation, we can limit the end user to have only certain number of characters that can be typed in a cell.

In our example, the sales rep address should not be greater than 20. So we are limiting the range of cells to be greater than (or) equal to 20. For ease, I have created a column name length which displays the length of the address for each sales rep.

Example - Limit # of characters in a cell

You can see that the address of the sales rep Walker (in cell B10) is greater than 20. So we are going to give a constraint.

Here's how you can limit the number of (text) characters that can be entered in a range of cells:

1. Select the range of cells you want to limit to a maximum number of characters
2. From the Data tab click Data Validation in the Data Tools group
3. In the Data Validation dialog box, select the Text length from the "Allow:" dropdown
4. From the Data dropdown select "less than or equal to"
5. In the Maximum field type the maximum number of characters allowed (or use a reference to a cell containing a number)
6. Click OK

Limit # of characters in a cell - Data validation

Now we have defined a constraint. But now this will not show an alert (or) error since the # of characters in cell B10 is greater than 20. For that, edit cell B10 and then press Enter, an error message will pop up. This will restrict the end user to use only 20 characters in a cell.

Limit # of characters in a cell - Data validation Created

Create Your Own Custom Data Validation Messages

Input Message

If you want, you can customize the messages the user sees. Data Validation allows you to create your own custom Input Message and Error Alert message.

By default, no message displays when a user merely selects a cell within a Data Validation range. However, when you apply Data Validation to a range of cells, it is often good practice to display a message to alert the user to the data validation requirements. 

1. In the Data Validation dialog box, select the Input Message tab
2. Place a checkmark in the 'Show input message when cell is selected' option
3. Type a message and message title that you want to display when a user clicks a cell within the Data Validation range
4. Click OK

Error Alert message

The default Error Alert message tells the userthat they've entered an invalid value in the range but it doesn't tell them why. Creating your own custom Error Alert allows you to explain to the user why the value they input was invalid.

1. In the Data Validation dialog box, select the Error Alert tab
2. Place a checkmark in the 'Show error alert after invalid data is entered' option
3. From the Style dropdown, choose a icon to be displayed in the Error Alert message dialog box
4. Enter a Title and Error message that you want to display if a user enters an invalid value
5. Click OK