How to limit characters length in a cell in Excel?
Sometimes you may want to limit how many characters a user can input to a cell. For example, you want to limit up to 10 characters can be inputted in a cell. This tutorial will shows you the details to limit characters in cell in Excel.
Kutools for Excel’s Advanced Sort utility provides us with much more flexible sorting methods: sort by text length, sort by last name, sort by absolute value, etc.
Recommended Productivity ToolsOffice Tab: Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Kutools for Excel: 200 new features for Excel, make Excel much easy and powerful, increase productivity immediately.
Step 1: Select the range that you will limit date entries with specify character length.
Step 2: Click the Data validation in the Data Tools group under Data tab.
Step 3: In the Data Validation dialog box, select the Text Length item from the Allow: drop down box. See the following screen shot:
Step 4: In the Data: drop down box, you will get a lot of choices and select one, see the following screen shot:
- If you want that others are only able to entry exact number of characters, says 10 characters, select the equal to item.
- If you want that the number of inputted character is no more than 10, select the less than item.
- If you want that the number of inputted character is no less than 10, select the greater than item.
Step 4: Entry exact number that you want to limit in Maximum/Minimum/Length box according to your needs.
Step 5: Click OK.
Now users can only enter text with limited number of characters in selected ranges.
|Prevent Duplicate: Prevent entering duplicate data in a single column with one click, extremely ease the settings of Data Validation in Excel. |
Read More Free Trial
The Data Validation allows us to set input message for text length limitation besides selected cell.
- Step 1: In the Data Validation dialog box, switch to the Input Message tab.
- Step 2: Check the Show input message when cell is selected option.
- Step 3: Entry the message title and message content.
- Step 4: Click OK.
Now go back to the worksheet, and click one cell in selected range with text length limitation, it displays a tip with the message title and content. See the following screen shot:
Another alternative way to tell user the cell is limited by text length is to set an error alter. The error alert will be shown after you entry invalid data.
- Step 1: In the Data Validation dialog box, switch to the Error Alert dialog box.
- Step 2: Check the Show error alert after invalid data in entered option.
- Step 3: Select the Warning item from the Style: drop down box.
- Step 4: Input the alert title and alert message.
- Step 5: Click OK.
Now if the text you entered in a cell is invalid, for example it contains more than 10 characters, a warning dialog box will pop up with preset alert title and message. See the following screen shot:
Comparing to setting data validation one by one, Kutools for Excel's Prevent Duplicate utility supports Excel users to prevent from duplicate entries in a list or a column with only one click. See screenshot:
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...