One of the amazing applications of Excel is data gathering. Excel can be used as a form or a questionnaire. After you gather some statistics, you can perform powerful analysis. When gathering data, you may enforce some limitations, such as the number of characters (character length) in a cell.
In this tutorial, we will learn how to limit character length in a cell in Excel.
Table of Contents
Limit Character Length
We can easily limit character length in Excel using Data Validation. Data Validation enables controlling/validating the type or characteristic of data entered in a cell. In this case, the feature that we’ll control is text length.
Let’s do it!
Step 1: Go to the Data tab in Excel.
Step 2: Select the cell you want to limit, and click on the Data Validation icon in the Data Tools part of the Excel ribbon.
The Data Validation window will open.
Step 3: In the Data Validation window, click on the dropdown menu under “Allow:”, and select “Text length”.
Step 4: Click on the dropdown menu under “Data:” and select “less than or equal to” if you want to set a maximum number of characters.
This will allow you to limit the number of characters to be less than or equal to a specified number.
Step 5: Type the maximum number of characters inside the “Maximum:” text box, and click OK.
Now the cell looks like a regular Excel cell, and you can enter anything you want:
When you exceed the number of maximum characters, a pop-up error will appear, indicating you have violated the data validation restrictions of the cell:
You can press Retry and enter text that has less than 10 characters.
Using the same steps, you can also select a minimum text length using the “greater than or equal to” setting. You can also set a minimum and maximum text length using the “between” setting:
This way, gathering data (or unsorting data included) in Excel is much more controlled.
In this tutorial, we learned how to limit character length in a cell in Excel using the Data Validation tool. Nice job!
Now that you know about limiting character length, limit the placement of these characters in the cells.