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: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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

Set Input Message for text length limitation

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:

Set Error Alert for text length limitation

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:

Related Article:

How to limit cell value entries in Excel?


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+1#NyPy2013-12-03 10:46
This is really helpful, is there anyway to make this count spaces too?
Reply | Reply with quote | Quote
+6#Rutger2013-12-17 16:23
The data validations to limit text length input are clear, but unfortunately validations stop the moment you copy text from another field which exceeds the max in the target field.

Can that be prevented in some way?

Would appricate userful respons!
Reply | Reply with quote | Quote
+3#Matthiasagreen2014-01-17 16:34
Use text to column, choose fixed width and choose the character count you want. It will separate antything above the limit to a new column that you can delete.
Reply | Reply with quote | Quote
0#Friday52014-01-31 15:26
Can you provide some instructions? Not sure how to accomplish what you are saying. What does "text to column" mean?
Reply | Reply with quote | Quote
+1#Bart2014-01-09 17:52
Thanks for the information.

Is it possible to limit existing text in a column to 30 characters and erase everything that exceeds that limited amount of characters?

Thank you
Reply | Reply with quote | Quote
+7#Matthiasagreen2014-01-17 16:34
Use text to column, choose fixed width and choose the character count you want. It will separate antything above the limit to a new column that you can delete.
Reply | Reply with quote | Quote
0#Mudassar2014-01-28 09:33
Really helpful. thanks a lot :-)
Reply | Reply with quote | Quote
0#Misty2014-02-20 15:27
Thank you! Very helpful!
Reply | Reply with quote | Quote
0#bachocron2014-02-27 18:53
This was great, nice step by step instructions!
Reply | Reply with quote | Quote
+1#RedHair4ever2014-03-19 18:23
Is it possible to use this with a scanner for barcode? After the scan is done (X digits) can "enter" be done automatically so it will positionned itself in the next cell so I can scan lots of items without to manually press enter ?

Any help will be welcome, I don't have any idea how to resolve this.
Reply | Reply with quote | Quote
0#Webstarr2014-04-03 16:25
Is it possible to set a data validation on a cell containing a concatenate formula? I am concatenating several cells' values and would like to warn the individual entering data if the count exceeds 50 characters. However, I don't want to use the =LEFT function as I need the user to edit his input values, rather than have Excel only return the first 50 characters.

Any ideas?
Reply | Reply with quote | Quote
0#JENN2014-05-02 21:15
use the following formula:
=LEFT(cell #,# of characters you want to limit the field down to)
Example:
=LEFT(C1,30)
Reply | Reply with quote | Quote
0#Roy2014-05-23 22:11
I need to limit numerical inputs to show just the last 5 characters regardless of string size.
can this be done in Excel?
Reply | Reply with quote | Quote
0#Smithd4762014-06-02 11:53
This design is incredible! You obviously know how to keep a reader amused. dakgffedggeddgfe
Reply | Reply with quote | Quote
0#Subhadeep2014-06-16 10:03
Very helpful, easy to learn. This was great, nice step by step instructions. Thank you.
Reply | Reply with quote | Quote
0#Vara Vemula2014-07-16 19:22
while converting txt to excel if the txt file contain a cell value more than 15 characcter it is rounding off. is there any formulat that helps to rpevent this.
Reply | Reply with quote | Quote

Add comment


Security code
Refresh