We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
There may be times when you want to limit how many characters can be entered in a cell or group of cells in Excel. Setting a limit on the number of characters that can be entered in some cells can help reduce inputting mistakes.
For example, if you need users to enter addresses, your spreadsheet may have a column for a 2 number U.S. state abbreviation. In this case, you may want to limit the characters entered in these cells to only 2.
Let’s pretend you need to limit the number of characters entered in a column to 2 characters. Here’s how you can set the character limits:
Use Data Validation to Set Character Limit in Excel
Click to Watch: How to Set Character Limit in Excel – Tutorial
You’ll find options for setting character limits in Excel cells in the Data tab of the Excel Ribbon, in the Data Tools section, in Data Validation.
To start, select the cell(s) for which you want to restrict the number of characters entered. This can be a range or an entire column or row.
Then, in the Data tab, in the Data Tools section, select Data Validation.
This will open the Data Validation pop-up window. Here is where you’ll set the limits for what can be typed in the cells you have selected.
Under Allow, select Text Length.
NOTE: the “Text Length” label can be a bit misleading because it can be used to restrict more than just text. It restricts the number of any character entered, meaning that it includes spaces, numbers, letters and symbols in its count.
Under Data, select how you’d like to restrict the character length. The best option for this example would be “Equal to,” but “Less than or equal to” could work too. You’ll have to decide what works best for your project.
In Length, type the number of characters for your limit. In our U.S. state abbreviation example, this would be 2.
These settings will limit the user to entering 2 characters in these cells. If they try to enter a different amount, they’ll receive an error message and will need to try again.
NEXT: Data Validation Error Messages in Excel (tutorial)
Leave a Reply