Skip to main content

How to limit characters length in an Excel cell?

Author: Kelly Last Modified: 2023-12-13

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

This section covers the steps to restrict the number of characters that can be entered into a specific cell or range of cells.

  1. Select the cell range where you want to enforce the character length limit.
  2. Click Data Validation in the Data Tools group under the Data tab.
  3. In the Data Validation dialog box, please set the validation rule as follows:
    1. From the Allow: drop-down menu, select Text Length.
    2. From the Data: dropdown, select the criteria based on your requirements:
      1. Choose the equal to option for an exact character count, e.g., 10 characters.
      2. Select less than or equal to for a maximum character limit, e.g., up to 10 characters.
      3. Opt for greater than for more than a specific count, e.g., more than 10 characters.
    3. In the Maximum/Minimum/Length: box, input the desired number for the character limit according to your needs.
  4. 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

With Kutools for Excel's Prevent Typing feature, you can easily limit character types in Excel cells or selections.
A. Block entry of special characters like *, !, etc.;
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:

  1. Select the cells where you will add an input message.
  2. Click Data Validation in the Data Tools group under the Data tab.
  3. In the Data Validation dialog box, select the Input Message tab, and do as follows:
    1. Check the Show input message when cell is selected option.
    2. Enter the message title and message content.
  4. 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:

  1. Select the cells where you want to activate an error alert for instances of invalid data entry.
  2. Click Data Validation in the Data Tools group under the Data tab.
  3. In the Data Validation dialog box, select the Error Alert tab, and do as follows:
    1. Check the Show error alert after invalid data in entered option.
    2. Select the Warning option from the Style: drop-down box.
    3. Enter the alert title and error message.
  4. 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


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

One click to prevent from entering duplicate data in a single column/list

Kutools for Excel's innovative Prevent Duplicate utility provides Excel users with the convenience of effortlessly preventing duplicate entries in a list or column. With just a single click, this powerful tool ensures data integrity and accuracy in your spreadsheets.

Related Article:

How to limit cell value entries in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (54)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you!
This comment was minimized by the moderator on the site
why date and number is allowed at text length?
This comment was minimized by the moderator on the site
Hi there, numbers are also characters. So if you set the max length less than 10 characters, as long as you don't input 10 or more number characters, you will be good.

Amanda
This comment was minimized by the moderator on the site
This is almost the exact solution I need but still need a bit more help. What I'm trying to achieve is to set a cell to have a max of 40 characters but not stop the user from entering all the data he needs, instead i would like anything over the 40 character limit to be populated in a second designated cell. Is this even a possibility? Thank you everyone in advance for any assistance provided.
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
I saw Tomas question about putting an exact limit of 10 spaces and your formula =A1&REPT(" ",10-LEN(A1)) however I need to take it a step further. I want to take three separate fields that I have set their spaces to exactly 10 and concatenate them with their "spaces" intact. Also, if I don't limit what they enter and they enter MORE than 10 characters in one of the cells I want to take ONLY the first 10. So as an example, I want to give them three cells they can enter information in. I don't want to limit what they input BUT, I want to concatenate these three fields and pick up 10 characters from each one. So if the first cell has 4 characters, in my concatenate formula I want it to pick up the 4 characters PLUS 6 spaces. If the second field has 20 characters, I want to only pick up the first 10 characters and the same thing for the third cell. We are trying to get to a uniform 30 characters of description but some names are longer or shorter than others. We want this to break evenly with 10 characters per cell. Hopefully this is making sense.
This comment was minimized by the moderator on the site
Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?
I want to set a cell to 10 character, when input 2 character then will auto fill up with 8 space after. if the cell is blank, then return with 10 space. this is for setting a excel file for user input and save as txt or cvs file for import to other software.
This comment was minimized by the moderator on the site
Hi Tomas,
You can use a formula to limit the text length: =A1 & REPT(" ",10-LEN(A1))
This comment was minimized by the moderator on the site
I want to limit the quantity of a cell depending on the category of another one,

For example if I input in A1 "Ok" in B1 must be limit to 10 characters
but if A1= "NG", B1 must be limit to 12 characters.
This comment was minimized by the moderator on the site
[quote]Hi, do you know how to put exact length limit 10 and when put abc i want from excel to put 8 space?By Ivan[/quote] I want to set a cell to 10 character, when input 2 character then will auto fill up with 8 space after. if the cell is blank, then return with 10 space. this is for setting a excel file for user input and save as txt or cvs file for import to other software
This comment was minimized by the moderator on the site
Hi, I have a attendance sheet. from 1 to 31. I put "P" on each cell if person is present. Now I want that how many times "P" is continuing present in cell. As as example - I have put "P" from 1 to 6 , then from 8th to 9th put P, and 10th is gap. then from 11th its continue to 18th. ... now i want how many times P is continue 6 time . PPPPPP PP PPPPPPPP Manually the answer is : 2(1to6 = 1,11to18=1) If you have any formula to count this it will be a great help.
This comment was minimized by the moderator on the site
=COUNTIF(B2:B17,">""") this formula will ignore empty cells but will count cells with data in i.e. P
This comment was minimized by the moderator on the site
Hi. I want out put txt file and no spaces between cell values. Like 3 cells with First Name, Middle and last. Entered Shawn G Goldman as SHAWNGGOLDMAN
This comment was minimized by the moderator on the site
Hi I want few things in a cell. I only want numbers in cell. I want to limit to 10 characters. I want to remove decimal like 15.00 to 1500. I want to indent to right. Also to add 0's to left to make it 10 characters .like 15.00 to 0000001500
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations